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.
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?
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.
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 :) )
Few years ago I spent lot of time to discover this trick with parallel constraint “creation” :)
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.
Also, the optimizer can take advantage of validated constraints better than non-validated ones
I totally like your videos. Big Kudos for your work.
But now most important: I need this shirt!!! 😂
Thank you! Shirt was from Singapore
thanks!pls more video like this
Always more to come!
intresting to see more issues in data warehouse enviroment 😎 sql and pl/sql😎
Thank you
You're welcome
❤
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?
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.
How to resolve this sql string ora -12847 error
It is a reparse warning. Try avoid DDL during the query runs
@@DatabaseDude give some examples
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 :) )
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 :-))
GG
o....k........