1,711 reputation
611
bio website sqlfail.com
location UK
age 34
visits member for 3 years, 6 months
seen Dec 22 at 12:44

I'm Chris Saxon, an Oracle database specialist with a track record of designing, implementing and optimising Oracle database applications.

I'm author of the database design quiz on Steven Feuerstein's PL/SQL Challenge website. I also blog at SQLfail - a site where I explain database concepts and other items that interest me using SQL.


Nov
24
awarded  Nice Answer
Jun
29
awarded  Yearling
Jun
24
awarded  Civic Duty
Feb
3
answered eCommerce orders table. Save prices, or use an audit/history table?
Nov
17
comment Oracle - How can I improve this query?
How many rows (on average) do you expect to be in payment for each (client, customerid)? How many rows are you expecting for the status = '020300'? Also, are you able to make any schema changes?
Sep
4
comment Oracle specifying join order hint
Can you show us the explain plan for the query, along with any index in place please?
Aug
26
answered Oracle intermittent performance problem
Aug
25
comment Oracle intermittent performance problem
Which version of Oracle are you using and are you licensed to use the diagnostic and tuning packs?
Aug
5
comment Trigger in trigger or trigger before table creation
Why don't you just run separate create table followed by create trigger statements? What's creating the trigger after being triggered by DDL giving you?
Jul
16
answered CONNECT BY using 3 tables(2 DIM and 1 FACT to get the Level and dependencies)
Jun
29
awarded  Yearling
Jun
28
comment Multiple JOIN of 6 tables via 5 database instances
@AndriyM - well spotted, I've updated the query; should work correctly now.
Jun
28
revised Multiple JOIN of 6 tables via 5 database instances
Fixed error in SQL query
Jun
27
comment Multiple JOIN of 6 tables via 5 database instances
If there are users with no privileges on either database the join to usr_all_users would enable you to list the user and show they have no roles. So there may be some benefit to including it in the query.
Jun
27
answered Multiple JOIN of 6 tables via 5 database instances
Jun
19
comment Creating and tuning a materialized view in oracle for use by Cognos BI
A materialized view that is just a select * over one table is no different from just applying the index to the table directly. For this to be any benefit you either need to be joining tables, calculating aggregates (sum, count, etc.) or applying a where clause to your table.
Jun
19
answered Which design to choose in this case?
Jun
17
comment Which design to choose in this case?
How many different combinations of x, y, z, dimension are there? How likely is it that you'll be recording more than one value against a given combination?
Jun
5
comment Index on foreign key makes query extremely slow
@ypercube - actually adding an index can invalidate the plans for associated tables, leading to a re-parse of queries on next execution. The optimizer can then generate a new plan for your query - which can be worse for various reasons. So just adding an index can hurt your queries.
Jun
4
comment Index on foreign key makes query extremely slow
To help answer your question, please post the explain plans for the good and bad plans; ideally using the gather_plan_statistics hint.