tag:blogger.com,1999:blog-34560741.post1122358114911993749..comments2023-11-02T12:22:23.089+03:00Comments on The Momen Blog: MIN and MAX Functions in a Single Query are DisastrousAsif Momenhttp://www.blogger.com/profile/08802175768050555784noreply@blogger.comBlogger8125tag:blogger.com,1999:blog-34560741.post-27443726556181047542008-08-07T13:25:00.000+03:002008-08-07T13:25:00.000+03:00Hi Hemant,Yes, after making column as NOT NULL, Or...Hi Hemant,<BR/><BR/>Yes, after making column as NOT NULL, Oracle Optimizer is picking "INDEX FAST FULL SCAN" instead of "TABLE ACCESS FULL".<BR/><BR/><BR/>SQL> alter table t modify (sno not null);<BR/><BR/>Table altered.<BR/><BR/>SQL> select min(sno), max(sno) from t;<BR/><BR/> MIN(SNO) MAX(SNO)<BR/>---------- ----------<BR/> 1 10000<BR/><BR/><BR/>Execution Plan<BR/>----------------------------------------------------------<BR/>Plan hash value: 1058879072<BR/><BR/>-------------------------------------------------------------------------------<BR/>| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |<BR/>-------------------------------------------------------------------------------<BR/>| 0 | SELECT STATEMENT | | 1 | 4 | 7 (0)| 00:00:01 |<BR/>| 1 | SORT AGGREGATE | | 1 | 4 | | |<BR/>| 2 | INDEX FAST FULL SCAN| T_IDX | 10000 | 40000 | 7 (0)| 00:00:01 |<BR/>-------------------------------------------------------------------------------<BR/><BR/>Thanks for sharing your thoughts.<BR/><BR/>RegardsAsif Momenhttps://www.blogger.com/profile/08802175768050555784noreply@blogger.comtag:blogger.com,1999:blog-34560741.post-73562820133129261152008-08-06T16:17:00.000+03:002008-08-06T16:17:00.000+03:00If you define the column as a NOT NULL, Oracle can...If you define the column as a NOT NULL, Oracle can use an IndexFullScan (but not FastFullScan).Hemant K Chitalehttps://www.blogger.com/profile/07369112096230549250noreply@blogger.comtag:blogger.com,1999:blog-34560741.post-80077807451003342172008-08-04T20:11:00.000+03:002008-08-04T20:11:00.000+03:00An IndexFullScan would be used if the column is de...An IndexFullScan would be used if the column is defined as NOT NULL.<BR/><BR/><BR/>A "hack" I used to create one more solution is to define a Materialized View (it can even by Refresh On Commit) to hold the MIN and MAX values in 2 columns and 1 row. Querying that MV is very fast !Hemant K Chitalehttps://www.blogger.com/profile/07369112096230549250noreply@blogger.comtag:blogger.com,1999:blog-34560741.post-11545355220691343542008-07-23T10:41:00.000+03:002008-07-23T10:41:00.000+03:00Even if either min or max function used on single ...Even if either min or max function used on single query, but calling twice e.g. "select min(c1), min(c1) from table", it will always not to use fast full index scan.Bundithttps://www.blogger.com/profile/15932387945449727016noreply@blogger.comtag:blogger.com,1999:blog-34560741.post-86997947593243810622008-07-23T10:08:00.000+03:002008-07-23T10:08:00.000+03:00Even if you use "select min(c1), min(c1) from tabl...Even if you use "select min(c1), min(c1) from table", it will always not to use fast full index scan".Bundithttps://www.blogger.com/profile/15932387945449727016noreply@blogger.comtag:blogger.com,1999:blog-34560741.post-60138774337061458702008-07-11T14:53:00.000+03:002008-07-11T14:53:00.000+03:00Hi,I would expected oracle to use a full index sca...Hi,<BR/><BR/>I would expected oracle to use a full index scan, as Karen Morten has showed on her blog.<BR/><BR/>You could try to do the query again, but this time creating the table with an additional "make_big" field (like char(100) or something)?<BR/>Probably this will cause oracle to use a full index scan (not as nice as an index full scan min/max, but normally better then a full table scan).Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-34560741.post-55849373834987558982008-07-10T18:39:00.000+03:002008-07-10T18:39:00.000+03:00also change your uindex to a primary key constrain...also change your uindex to a primary key constraint and It'll do an index fast full scan rather than a tablescan. <BR/>Not sure why it doesn't do this with a unique index.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-34560741.post-47559432237704326042008-07-10T17:55:00.000+03:002008-07-10T17:55:00.000+03:00Try either of the UNION or Inline View methods I'v...Try either of the UNION or Inline View methods I've shown at <BR/><BR/>http://hemantoracledba.blogspot.com/2007/08/when-cost-doesnt-indicate-true-load.htmlHemant K Chitalehttps://www.blogger.com/profile/07369112096230549250noreply@blogger.com