SQL and Database explained!

Пікірлер

@iejjeeeeeedjsnckdsn 12 сағат бұрын

As a Java programmer, I always struggle with SQL data flow. I kind of 'invented' chained 'WITH' clauses to reason about queries. So I looked it up and found this video. Great summary of why it's useful.

@vijaybhai4866 20 сағат бұрын

Thankyou. Locks has been the chapter I skipped whenever I read the manual. Now I know the unique advantage of oracle which non other DB have 🙏🏻🤔

@itshakgm 21 сағат бұрын

Hi. Thanks your information. I think that yet exists an issue/lot of limitations with dblink and clob. In your example, the user querying the clob is the same user that created the dblink but if you try to permit other user (giving privilege or creating view ), you will receive other ORAs like Ora- 2019.

@steveandreassend8620 2 күн бұрын

Surely there is a case to make option 3 native to the product so it is all transparent? It seems to be the only elegant solution for this problem.

@steveandreassend8620 2 күн бұрын

ALTER TABLE x TRACK PRIMARY KEY PARTITION; and do some sort of background predicate appending like RLS / VPD does.

@DatabaseDude Күн бұрын

I certainly like the concept of that being a declarative part of the database

@dhirajtagadapallewar8475 4 күн бұрын

Very nicely explained. Please provide commands to try in test environment

@vikram4755 5 күн бұрын

Wow !

@ppaolucc 5 күн бұрын

Hi Connor, maybe I'm a bit wrong, however: if we would create a global partitioned index (I've seen here a creation of a Global non-partitioned one) we would be able to do partition pruning, even though an maintenance action on partitions (MOVE, DROP, SPLIT, TRUNCATE) would affect the validity of the entire index as well, correct? Thank you

@DatabaseDude 4 күн бұрын

With older versions yes - no we have the ability to mark "parts" of the index not usable

@ricardooberdanpereira793 6 күн бұрын

How one person can know so much about? Thanks again Connor.

@sergiidziubenko3313 6 күн бұрын

Few years ago I spent lot of time to discover this trick with parallel constraint “creation” :)

@fransc2086 7 күн бұрын

Great explanation! Thanks!

@DatabaseDude 4 күн бұрын

Glad it was helpful!

@gleytonr 7 күн бұрын

Thanks so much

@DatabaseDude 4 күн бұрын

You're welcome!

@marcinbadtke 7 күн бұрын

Thank you. Fantastic idea.

@DatabaseDude 4 күн бұрын

Glad you liked it!

@ilpaolotopleggend6663 7 күн бұрын

Thanks !super video super issue super explanation

@DatabaseDude 4 күн бұрын

Welcome 😊

@ErickTruter 8 күн бұрын

Great video! Partitions is great but I find often people don't understand the costs involved in using them and often don't use them correctly causing all partition scans.

@DatabaseDude 4 күн бұрын

Very true!

@ilpaolotopleggend6663 8 күн бұрын

I'd like also to see more PLSQL code..understand examples with asynchronous cases..and queue.. 💻 ⌛️😮

@ilpaolotopleggend6663 8 күн бұрын

😮interesting

@DatabaseDude 4 күн бұрын

I'm hoping we de-deprecate it (if that makes sense)

@ilpaolotopleggend6663 8 күн бұрын

😊

@ilpaolotopleggend6663 13 күн бұрын

thanks

@DatabaseDude 4 күн бұрын

You're welcome!

@gvarph7212 17 күн бұрын

What is your opinion on surrogate vs natural composite key? For example, if every record can be uniquely described with a combination of 4 columns in a way that makes sense, is it still worth worth it to go for the natural composite key? I personally prefer surrogate keys in this situation because it often ends up saving quite a lot of space if you ever have to other large tables that would have the first table linked though foreign keys

@DatabaseDude 15 күн бұрын

I think my video covers this... its about being pragmatic. Without knowing the context, a natural key of 4 columns for a table with no children (and unlikely to do so) might be fine. If its going to have children, I'd probably opt for a surrogate to avoid carrying all that information down the chain

@MikeCulpav8r 19 күн бұрын

Does this carry forward to version 19c, 21c, and 23c?

@DatabaseDude 15 күн бұрын

Yes - all versions since 12.2 will allow this

@MikeCulpav8r 15 күн бұрын

@@DatabaseDude I followed your example to the letter and it seems too easy, I have a customer that I would like to show the difference between starting with BASICFILES and converting to SECUREFILES and what the size impact would be if we "left" it as BASICFILES and did the deletes to show that the SECUREFILES conversion actually doesn't hold the space allocated after deletes......I will try and modify your example to show the differences....thank you so much for your examples, you are awesome!!!!!!!

@rahulkurkute1680 20 күн бұрын

I want tooooooo❤❤❤❤

@ilpaolotopleggend6663 20 күн бұрын

Complex for me but happy to learn by the best

@DatabaseDude 15 күн бұрын

hopefully it didnt dive too deep

@ilpaolotopleggend6663 15 күн бұрын

I get the sense of it but not sure about latch chain buffer cache how works internally ecc

@ilpaolotopleggend6663 20 күн бұрын

Great!

@bhavindave3003 21 күн бұрын

For straight 1 min, I was looking at t-shirt wondering where I can get one.

@DatabaseDude 15 күн бұрын

unfortunately it is a 1 of 1

@ilpaolotopleggend6663 21 күн бұрын

Thanks, very clear explanation

@DatabaseDude 15 күн бұрын

Glad it was helpful!

@ilpaolotopleggend6663 22 күн бұрын

thanks!pls more video like this

@DatabaseDude 22 күн бұрын

Always more to come!

@ilpaolotopleggend6663 21 күн бұрын

intresting to see more issues in data warehouse enviroment 😎 sql and pl/sql😎

@bahaeddinehemmemCHAREN 23 күн бұрын

does it work on arch linux?

@DatabaseDude 22 күн бұрын

"Maybe" but not supported. I would imagine the preinstall rpm should either set everything up you need...or complain loudly and stop :-)

@marcinbadtke 24 күн бұрын

Thank you

@DatabaseDude 22 күн бұрын

You're welcome

@andreas-wismann 25 күн бұрын

Is it feasible (i.e. a good idea) to leave the constraint in the NOVALIDATE state forever, for example if I cannot resolve 100% of the existing rows using the foreign key, but want to ensure that at least the new data is valid?

@DatabaseDude 25 күн бұрын

Feasible ... yes. Advisable ... generally no, because the optimizer can do better things if it knows the data is valid. There is an option to use the RELY clause which tells the optimizer that things are valid even if they are not, but of course that then runs the risk of getting invalid results.

@khmansour1 26 күн бұрын

❤

@JochenVandenBossche 26 күн бұрын

When looking for _manuals_, I often add search term "release 19" to constrain the results to that version.

@prakash5935 26 күн бұрын

How to resolve this sql string ora -12847 error

@DatabaseDude 26 күн бұрын

It is a reparse warning. Try avoid DDL during the query runs

@prakash5935 26 күн бұрын

@@DatabaseDude give some examples

@davidpasternak341 27 күн бұрын

I totally like your videos. Big Kudos for your work. But now most important: I need this shirt!!! 😂

@DatabaseDude 27 күн бұрын

Thank you! Shirt was from Singapore

@mustafakalayciDBA 28 күн бұрын

your new laptop allows you to run many parallel sessions Conner :D (maybe your new laptop become old, I don't know, I do remember that you were looking for a new one on twitter :) )

@DatabaseDude 27 күн бұрын

8 cores in my Asus G14. That's ample to get things moving along. (20 cores in the home desktop if I really need it :-))

@marcelvanLare 28 күн бұрын

Nice. Once i thought to be clever and used not null novalidate as a constraint to stay, so that only new data would need to follow that. Only the other party would do a full refresh delete and insert on the table. That was a production issue then. I learned my lesson to have this only for temporary situations.

@DatabaseDude 28 күн бұрын

Also, the optimizer can take advantage of validated constraints better than non-validated ones

@virajmokadam4719 28 күн бұрын

Thanks. If we use bulk collect in plsql with fetch size >100, will it be more beneficial?

@DatabaseDude 28 күн бұрын

"Depends" .... for example, a slow network might benefit from larger sizes. Typically 100-200 is the sweet spot with diminishing benefits after that

@virajmokadam4719 28 күн бұрын

@@DatabaseDude ok

@pavanmadamset 28 күн бұрын

Thank You very much Connor for the interesting hacks to avoid delays for DMLs by "enable novalidate" for adding and "online"for dropping

@ViviCraft-rg5it 29 күн бұрын

@DatabaseDude 27 күн бұрын

o....k........

@khmansour1 29 күн бұрын

❤

@viswapsp 29 күн бұрын

Thank you, Connor. Very similar video is here kzbin.info/www/bejne/j2KyaH-Kn82Dhq8. My question is does the KEEP clause work to output the names of the cities by country with the highest population, assuming there may be more than one city in a country with the same highest population? If that is not possible, I may lean towards using our regular way of doing as it works in that case as well.

@DatabaseDude 28 күн бұрын

Unfortunately LISTAGG is not supported as a KEEP function currently

@viswapsp 29 күн бұрын

Connor - Good morning. Thank you for helping with your knowledge sharing. I received the desired result when there is ONLY ONE desired record per group, which in my opinion not practical. I would like to understand if it can give me the list of names of the employees (more than one employee) per department who are making the max salary in that department.

@DatabaseDude 28 күн бұрын

see my answer to your other comment

@akhilchintu6938 Ай бұрын

Hello Sir, My database is not coming up and not able to open due to ORA-01157:datafile corruption. errors: ORA-01157: cannot identify/lock data file 22 - see DBWR trace file ORA-01110: data file 22: 'datafile/location' And Assumptions are, 1) DB is non-archive log mode 2) DB is in mount stage 3) no RMAN backup 4) its a nonsytem datafile How to recover the datafile by following above assumptions and open the database? Kindly help me out of this.

@DatabaseDude 28 күн бұрын

asktom.oracle.com/ords/asktom.search?tag=unable-to-open-the-database-ora-01157-cannot-identifylock-data-file

@valizeth4073 Ай бұрын

I can't express how much I hate when people use a prefix when it makes no sense. 10 000 milliamps = 10 * 10^3 * 10^(-3) amps,... just say 10 amps.

@BertieJasokie Ай бұрын

A watt hour rating is the best.

@DatabaseDude Ай бұрын

What? #SeeWhatIDidThere :-)

@mikeanagnostou Ай бұрын

Hi Conor, thanks for the explanation, always found the keep puzzling, one question: You said max is not needed, but what if we have multiple records with the same maximum distance? Wouldn't in that case an aggregate on the wind make sense so we can select the maximum (or minimum) wind that created the largest jump(s), does this make sense? cheers from Toronto

@DatabaseDude Ай бұрын

Yes, it could be used for ties, but I'd contend in most cases (since the true max/min is some other columns) what does "max" means in terms of wind here? In this case, it would be an alphabetic sort - does it really mean anything? And if it DID have meaning, then I'd be including it in the ORDER BY to ensure no concept of ties occurs

@vikram4755 Ай бұрын

May be oracle should start throwing compile error.. sating NO 😅

@DatabaseDude Ай бұрын

If you set plsql warnings to highest level I think we do that :-)

@vikram4755 Ай бұрын

@@DatabaseDude 🙏

@ahmedinomazic7032 Ай бұрын

Great info but can this be used in function, as i see in v$sql most of rows p./fetches are from functions which some basic select count(*) ?

@DatabaseDude Ай бұрын

sorry I don't quite understand what you are asking

@ahmedinomazic7032 Ай бұрын

@@DatabaseDude In my database in v$sql most of fetches is from functions if I set up for i in ()... would it benefit , would the latches reduce ?

@DatabaseDude 28 күн бұрын

@@ahmedinomazic7032 without seeing the context, its hard to say, but probably yes

@berndeckenfels Ай бұрын

Hm isn’t the Jdbc driver also using array fetches when you iterate a ResultSet

@DatabaseDude Ай бұрын

You choose the size. Older drivers used 2 (the minimum) and I think we now default to 10.

@JimmyBrock Ай бұрын

Love these deep dives

@marcinbadtke Ай бұрын

Thank you Connor. Inspiring.

@MrEnrique2k Ай бұрын

Love it

Ең жақсы KZbin

Пікірлер