StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>The others are right, you shouldn't use a numeric column like that.</p> <p>However, it is actually, the <code>OR <subquery></code> construct that is causing a (performance) problem in this case. I don't know if it is different in version 11, but up to version 10gr2, it causes a a filter operation with what is basically a nested loop with a correlated subquery. In your case, the use of a numeric column as a varchar also results in a full table scan.</p> <p>You can rewrite your query like this:</p> <pre><code>select * from big where id like '45%' union all select * from big join small using(id) where id not like '45%'; </code></pre> <p>With your test case, I end up with a row count of 174000 rows in big and 9 small. Running your query takes 7 seconds with 1211399 consistent gets. Running my query 0,7 seconds and uses 542 consistent gets.</p> <p>The explain plans for my query is:</p> <pre><code> -------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| --------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 8604 | 154 (6)| | 1 | UNION-ALL | | | | |* 2 | TABLE ACCESS FULL | BIG | 8603 | 151 (4)| | 3 | NESTED LOOPS | | 1 | 3 (0)| |* 4 | TABLE ACCESS FULL | SMALL | 1 | 3 (0)| | 5 | TABLE ACCESS BY INDEX ROWID| BIG | 1 | 0 (0)| |* 6 | INDEX UNIQUE SCAN | BIG_PK | 1 | 0 (0)| --------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(TO_CHAR("ID") LIKE '45%') 4 - filter(TO_CHAR("SMALL"."ID") NOT LIKE '45%') 6 - access("BIG"."ID"="SMALL"."ID") Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 542 consistent gets 0 physical reads 0 redo size 33476 bytes sent via SQL*Net to client 753 bytes received via SQL*Net from client 76 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1120 rows processed </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

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