Databases
StackOverflow2013
Postsdbo
POImproving SQL Exists scalability
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POImproving SQL Exists scalability
    primarykey
    data
    text
    <p>Say we have two tables, TEST and TEST_CHILDS in the following way:</p> <pre><code>creat TABLE TEST(id1 number PRIMARY KEY, word VARCHAR(50),numero number); creat TABLE TEST_CHILD (id2 number references test(id), word2 VARCHAR(50)); CREATE INDEX TEST_IDX ON TEST_CHILD(word2); CREATE INDEX TEST_JOIN_IDX ON TEST_CHILD(id); insert into TEST SELECT ROWNUM,U1.USERNAME||U2.TABLE_NAME, LENGTH(U1.USERNAME) FROM ALL_USERS U1,ALL_TABLES U2; INSERT INTO TEST_CHILD SELECT MOD(ROWNUM,15000)+1,U1.USER_ID||U2.TABLE_NAME FROM ALL_USERS U1,ALL_TABLES U2; </code></pre> <p>We would like to query to get rows from TEST table that satisfy some criteria in the child table, so we go for:</p> <pre><code>SELECT /*+FIRST_ROWS(10)*/* FROM TEST T WHERE EXISTS (SELECT NULL FROM TEST_CHILD TC WHERE word2 like 'string%' AND TC.id = T.id ) AND ROWNUM &lt; 10; </code></pre> <p>We always want just the first 10 results, not any more at all. Therefore, we would like to get the same response time to read 10 results whether table has 10 matching values or 1,000,000; since it could get 10 distinct results from the child table and get the values on the parent table (or at least that is the plan that we would like). But when checking the actual execution plan we see:</p> <pre><code>----------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 54 | 5 (20)| 00:00:01 | |* 1 | COUNT STOPKEY | | | | | | | 2 | NESTED LOOPS | | | | | | | 3 | NESTED LOOPS | | 1 | 54 | 5 (20)| 00:00:01 | | 4 | SORT UNIQUE | | 1 | 23 | 3 (0)| 00:00:01 | | 5 | TABLE ACCESS BY INDEX ROWID| TEST_CHILD | 1 | 23 | 3 (0)| 00:00:01 | |* 6 | INDEX RANGE SCAN | TEST_IDX | 1 | | 2 (0)| 00:00:01 | |* 7 | INDEX UNIQUE SCAN | SYS_C005145 | 1 | | 0 (0)| 00:00:01 | | 8 | TABLE ACCESS BY INDEX ROWID | TEST | 1 | 31 | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM&lt;10) 6 - access("WORD2" LIKE 'string%') filter("WORD2" LIKE 'string%') 7 - access("TC"."ID"="T"."ID") </code></pre> <p><strong>SORT UNIQUE</strong> under the <strong>STOPKEY</strong>, what afaik means that it is reading all results from the child table, making the distinct to finally select only the first 10, making the query not as scalable as we would like it to be.</p> <p>Is there any mistake in my example?</p> <p>Is it possible to improve this execution plan so it scales better?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USMario Corchero
    1. USMario Corchero
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POImproving SQL Exists scalability
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POImproving SQL Exists scalability
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POImproving SQL Exists scalability
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload