Home » RDBMS Server » Performance Tuning » Bucketing Query Performance
Bucketing Query Performance [message #309777] Fri, 28 March 2008 14:32 Go to next message
verpies
Messages: 28
Registered: March 2008
Location: Seattle
Junior Member
Dear All,

I need your help with speeding up this bucketing query for Oracle 10.2.0.3.0

SELECT i,FNAME,SNAME,DOB
FROM (
        SELECT /*+ INDEX_ASC(CUSTOMER IDX_USE_ME)*/
          row_number() OVER (PARTITION BY FNAME,SNAME ORDER BY FNAME,SNAME,ID) i,
          FNAME,
          SNAME,
          DOB,
          rownum rnum
        FROM CUSTOMER
        WHERE FNAME IS NOT NULL AND SNAME IS NOT NULL         
        ORDER BY FNAME,SNAME,ID
     )
WHERE mod(rnum,:RPB)<3;
Where :RPB denotes RowsPerBucket = (SELECT count(*) FROM customer) / 100

The purpose of this query is to divide all the non-null rows of the CUSTOMER table sorted by FNAME,SNAME,ID into 100 evenly-sized buckets and then to return only the first 3 rows from each bucket.
The "i" column is a disambiguation column that consecutively numbers all the rows with the same FNAME and SNAME.
The :RPB variable is known ahead of time.

Please do not concern yourself with cases where the count of table rows is not divisible by 100.


Regards,
George Robinson

Here is a testcase. Table & Data are the same as with the huge query before, but any data could do for this test.
SQL> DROP INDEX IDX_USE_ME;
Index dropped.

SQL> CREATE UNIQUE INDEX IDX_USE_ME ON CUSTOMER (FNAME, SNAME , ID);
Index created.

SQL> exec DBMS_STATS.GATHER_INDEX_STATS (ownname => 'ORAUSER', indname => 'IDX_USE_ME');
PL/SQL procedure successfully completed.

SQL> exec DBMS_STATS.GATHER_TABLE_STATS (ownname => 'ORAUSER', tabname => 'CUSTOMER');
PL/SQL procedure successfully completed.

SQL> 
SQL> SELECT i,FNAME,SNAME,DOB
  2  FROM (
  3           SELECT /*+ INDEX_ASC(CUSTOMER IDX_USE_ME)*/
  4              row_number() OVER (PARTITION BY FNAME,SNAME ORDER BY FNAME,SNAME,ID) i,
  5              SNAME,FNAME,DOB,rownum rnum
  6           FROM CUSTOMER
  7           WHERE FNAME IS NOT NULL AND SNAME IS NOT NULL         
  8           ORDER BY FNAME,SNAME,ID
  9       )
 10  WHERE mod(rnum,506)<3;

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 @2-07-18:08:04:                                    SI_COLORHISTOGRAM                                                                19-NOV-06
         1 @2-10-23:15:20:                                    AQ$_JMS_MAP_MESSAGES                                                             19-NOV-06
         2 @2-10-23:15:20:                                    AQ$_JMS_MAP_MESSAGES                                                             19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 @6-11-19:10:54:                                    /7a628fb8_DefaultHSBChooserPan                                                   19-NOV-06
         1 @6-11-19:10:54:                                    /7aa898c9_BasicFileChooserUIGo                                                   19-NOV-06
         1 @6-11-19:10:56:                                    java/awt/TextArea                                                                19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 @6-11-19:10:56:                                    java/awt/Window$1DisposeAction                                                   19-NOV-06
         1 @6-11-19:10:56:                                    java/awt/event/FocusAdapter                                                      19-NOV-06
         1 @6-11-19:11:03:                                    oracle/ODCI/ODCIIndexCtx                                                         19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 @6-11-19:11:03:                                    oracle/ODCI/ODCIIndexInfo                                                        19-NOV-06
         1 @6-11-19:11:03:                                    oracle/expfil/ParamsParser                                                       19-NOV-06
         1 A6-11-19:10:29:                                    DBA_OPBINDINGS                                                                   19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 A6-11-19:10:29:                                    DBA_SECONDARY_OBJECTS                                                            19-NOV-06
         1 A6-11-19:10:29:                                    DBA_UNUSED_COL_TABS                                                              19-NOV-06
         1 A6-11-19:10:54:                                    sqlj/codegen/ExecCodegen                                                         19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 A6-11-19:10:54:                                    sqlj/codegen/ParseletFactory                                                     19-NOV-06
         1 A6-11-19:10:54:                                    sqlj/codegen/TempDecl                                                            19-NOV-06
         1 A6-11-19:10:57:                                    /5a41f72c_UTF8ValidationFilter                                                   19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 A6-11-19:10:57:                                    /5a696e48_PagesPerMinute                                                         19-NOV-06
         1 A6-11-19:10:57:                                    /5bd3aa59_ExtensionInstallatio                                                   19-NOV-06
         1 A6-11-19:11:11:                                    /cdd2f2c1_BMPDescriptor                                                          19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 A6-11-19:11:11:                                    /de10413_AndOpImage                                                              19-NOV-06
         1 A6-11-19:11:11:                                    /e4479ba2_TiffTagAdapter                                                         19-NOV-06
         1 B6-11-19:10:33:                                    AQ$_SCHEDULER$_JOBQTAB_I                                                         19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 B6-11-19:10:33:                                    DBA_SCHEDULER_RUNNING_CHAINS                                                     19-NOV-06
         1 B6-11-19:10:33:                                    DBA_SCHEDULER_WINDOWS                                                            19-NOV-06
         1 B6-11-19:10:55:                                    /6f4b0289_AttributeGen                                                           19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 B6-11-19:10:55:                                    /71461172_ModuleEntry                                                            19-NOV-06
         1 B6-11-19:10:55:                                    /75721cff_TypeErrorType                                                          19-NOV-06
         1 B6-11-19:10:57:                                    java/io/PrintWriter                                                              19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 B6-11-19:10:57:                                    java/io/StringReader                                                             19-NOV-06
         1 B6-11-19:10:57:                                    java/io/Win32FileSystem                                                          19-NOV-06
         1 B6-11-19:11:26:                                    GENCURSORBLOCKSEQUENCE                                                           19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 B6-11-19:11:26:                                    GENCURSORSPECIFIERSTRUCT                                                         19-NOV-06
         1 B6-11-19:11:26:                                    GENDEFINITIONPOST92UNION                                                         19-NOV-06
         1 C6-11-19:10:41:                                    TEMPLATE$_TARGETS_PK                                                             19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 C6-11-19:10:41:                                    USER_REPCATLOG                                                                   19-NOV-06
         1 C6-11-19:10:41:                                    USER_REPCOLUMN                                                                   19-NOV-06
         1 C6-11-19:10:55:                                    /eff941fe_RSAKey                                                                 19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 C6-11-19:10:55:                                    /f1074797_InterceptorInvoker                                                     19-NOV-06
         1 C6-11-19:10:55:                                    /f27212bf_GenericURLDirContext                                                   19-NOV-06
         1 C6-11-19:10:58:                                    /48429d3_SerProfileToClassErro                                                   19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 C6-11-19:10:58:                                    /5a4138b2_jndiproviderproperti                                                   19-NOV-06
         1 C6-11-19:10:58:                                    /5e951bce_LocaleElements_th                                                      19-NOV-06
         1 C6-11-19:11:36:                                    /554b5878_TimeZone                                                               19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         2 C6-11-19:11:36:                                    /554b5878_TimeZone                                                               19-NOV-06
         1 C6-11-19:11:36:                                    /5ec5f173_JGeoRasterSRS                                                          19-NOV-06
         1 D6-11-19:10:53:                                    /540bc759_MutableAttrListImpl                                                    19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 D6-11-19:10:53:                                    /57000ead_FactoryFinder                                                          19-NOV-06
         1 D6-11-19:10:53:                                    /5b10dc5_XNodeSet                                                                19-NOV-06
         1 D6-11-19:10:56:                                    /383f88fa_HTMLDocumentHTMLRead                                                   19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 D6-11-19:10:56:                                    /38e6f422_ContainerEventTarget                                                   19-NOV-06
         1 D6-11-19:10:56:                                    /413926ef_HiddenTagViewEndTagB                                                   19-NOV-06
         1 D6-11-19:10:58:                                    sun/security/util/Resources_sv                                                   19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 D6-11-19:10:58:                                    sun/tools/jar/SignatureFile                                                      19-NOV-06
         1 D6-11-19:10:59:                                    LocaleData_coll_ja_JP                                                            19-NOV-06
         1 D7-11-07:03:41:                                    SALES                                                                            07-NOV-07

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 D7-11-07:03:41:                                    SALES_CHANNEL_BIX                                                                07-NOV-07
         1 D7-11-07:03:41:                                    SALES_CUST_BIX                                                                   07-NOV-07
         1 E6-11-19:10:54:                                    /7125a7ad_ObjectStatement                                                        19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 E6-11-19:10:54:                                    /71e9a4f9_CharExpression                                                         19-NOV-06
         1 E6-11-19:10:54:                                    /78209d9_MetalTitlePaneTitlePa                                                   19-NOV-06
         1 E6-11-19:10:56:                                    COM/rsa/jsafe/SunJSSE_fe                                                         19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 E6-11-19:10:56:                                    COM/rsa/jsafe/SunJSSE_q                                                          19-NOV-06
         1 E6-11-19:10:56:                                    HTTPClient/BasicCookieBox$1                                                      19-NOV-06
         1 E6-11-19:11:02:                                    oracle/qsma/TokenMgrError                                                        19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 E6-11-19:11:02:                                    oracle/xquery/XQException                                                        19-NOV-06
         1 E6-11-19:11:02:                                    oracle/xquery/comp/QueryMgr                                                      19-NOV-06
         1 F6-11-19:10:28:                                    USER_IND_EXPRESSIONS                                                             19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 F6-11-19:10:28:                                    USER_PASSWORD_LIMITS                                                             19-NOV-06
         1 F6-11-19:10:28:                                    USER_TABLES                                                                      19-NOV-06
         1 F6-11-19:10:54:                                    /f05e77f8_NavSchemaObjectFacto                                                   19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 F6-11-19:10:54:                                    /f0c95e76_T4C8TTIBfile                                                           19-NOV-06
         1 F6-11-19:10:54:                                    /f143cb63_BasicProgressBarUICh                                                   19-NOV-06
         1 F6-11-19:10:57:                                    /15795111_basic                                                                  19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 F6-11-19:10:57:                                    /15c1592c_ParserAdapter                                                          19-NOV-06
         1 F6-11-19:10:57:                                    /167106dd_CertPathBuilder                                                        19-NOV-06
         1 F6-11-19:11:11:                                    /386133af_DescriptorFactory                                                      19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 F6-11-19:11:11:                                    /3fa9ba61_ExpDescriptor                                                          19-NOV-06
         1 F6-11-19:11:11:                                    /403acb0c_OrdBlobSeekableSourc                                                   19-NOV-06
         1 G6-11-19:10:31:                                    UTL_HTTP                                                                         19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 G6-11-19:10:31:                                    UTL_I18N                                                                         19-NOV-06
         1 G6-11-19:10:31:                                    UTL_NLA_ARRAY_INT                                                                19-NOV-06
         1 G6-11-19:10:55:                                    /517cc1bc_AlreadyBoundHelper                                                     19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 G6-11-19:10:55:                                    /51e48399_ServerHeldDown                                                         19-NOV-06
         1 G6-11-19:10:55:                                    /5321a108_Activator                                                              19-NOV-06
         1 G6-11-19:10:57:                                    /c4d983a3_ClassDeclarationStac                                                   19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 G6-11-19:10:57:                                    /c73108d1_CharacterConverter2B                                                   19-NOV-06
         1 G6-11-19:10:57:                                    /c860a039_ConsoleHandler                                                         19-NOV-06
         1 G6-11-19:11:14:                                    /4b0441f6_CodecUtils                                                             19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 G6-11-19:11:14:                                    /6460eb01_TIFFFaxDecoder                                                         19-NOV-06
         1 G6-11-19:11:14:                                    /6945eb77_TIFFHandler1                                                           19-NOV-06
         1 H6-11-19:10:39:                                    V_$DATAPUMP_JOB                                                                  19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 H6-11-19:10:40:                                    AQ$KUPC$DATAPUMP_QUETAB_S                                                        19-NOV-06
         1 H6-11-19:10:40:                                    DBA_LOGSTDBY_LOG                                                                 19-NOV-06
         1 H6-11-19:10:55:                                    /ca4179be_XConstants                                                             19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 H6-11-19:10:55:                                    /cad56a18_GIOPImpl1                                                              19-NOV-06
         1 H6-11-19:10:55:                                    /cebc8509_InvalidResolverPatte                                                   19-NOV-06
         1 H6-11-19:10:57:                                    sun/misc/FileURLMapper                                                           19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 H6-11-19:10:57:                                    sun/misc/URLClassPath$3                                                          19-NOV-06
         1 H6-11-19:10:57:                                    sun/net/www/MimeTable$1                                                          19-NOV-06
         1 H6-11-19:11:28:                                    MRV_OLAP_CWM1_AGGOP                                                              19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 H6-11-19:11:28:                                    XML_LOAD_RECORDS                                                                 19-NOV-06
         1 H6-11-19:11:29:                                    CWM2_OLAP_AW_AWUTIL                                                              19-NOV-06
         1 I6-11-19:10:52:                                    WM$CURRENT_WORKSPACE_VIEW                                                        19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 I6-11-19:10:52:                                    WM$PARCONFLICT_HIERARCHY_VIEW                                                    19-NOV-06
         1 I6-11-19:10:52:                                    WM$TABLE_VERSIONS_IN_LIVE_VIEW                                                   19-NOV-06
         1 I6-11-19:10:55:                                    sun/tools/tree/LessExpression                                                    19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 I6-11-19:10:55:                                    sun/tools/tree/OrExpression                                                      19-NOV-06
         1 I6-11-19:10:56:                                    /1169ca20_AdapterActivator                                                       19-NOV-06
         1 I6-11-19:10:58:                                    sun/io/ByteToCharCp875                                                           19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 I6-11-19:10:58:                                    sun/io/ByteToCharCp948                                                           19-NOV-06
         1 I6-11-19:10:58:                                    sun/io/ByteToCharGBK                                                             19-NOV-06
         1 I6-11-19:11:39:                                    SMP_EMD_DBSITEMAP_OBJ                                                            19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 I6-11-19:11:39:                                    SYS_C005080                                                                      19-NOV-06
         1 I6-11-19:11:40:                                    MGMT$CLUSTER_INTERCONNECTS                                                       19-NOV-06
         1 J6-11-19:10:54:                                    /40c3b2ff_BasicInternalFrameTi                                                   19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 J6-11-19:10:54:                                    /4173215_DynamicProfile                                                          19-NOV-06
         1 J6-11-19:10:54:                                    /4262d3ab_OracleClobWriter                                                       19-NOV-06
         1 J6-11-19:10:56:                                    /d2d6fe5c_PolicyFile3                                                            19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 J6-11-19:10:56:                                    /d343fdf4_RMIHttpToPortSocketF                                                   19-NOV-06
         1 J6-11-19:10:56:                                    /d54e8448_StyledEditorKitStyle                                                   19-NOV-06
         1 J6-11-19:11:01:                                    oracle/xml/xslt/XSLCondition                                                     19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 J6-11-19:11:01:                                    oracle/xml/xslt/XSLConstants                                                     19-NOV-06
         1 J6-11-19:11:01:                                    oracle/xml/xslt/XSLOutput                                                        19-NOV-06
         1 K6-11-19:10:28:                                    GV$AW_SESSION_INFO                                                               19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 K6-11-19:10:28:                                    GV$BACKUP_ASYNC_IO                                                               19-NOV-06
         1 K6-11-19:10:28:                                    GV$HS_PARAMETER                                                                  19-NOV-06
         1 K6-11-19:10:54:                                    /de186e24_BasicPopupMenuUIBasi                                                   19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 K6-11-19:10:54:                                    /deeccaa2_OraClosureFactory16                                                    19-NOV-06
         1 K6-11-19:10:54:                                    /e1bcd77c_MetalIconFactoryFile                                                   19-NOV-06
         1 K6-11-19:10:56:                                    javax/swing/text/SegmentCache                                                    19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 K6-11-19:10:56:                                    javax/swing/text/StyleContext                                                    19-NOV-06
         1 K6-11-19:10:56:                                    javax/swing/text/View                                                            19-NOV-06
         1 K6-11-19:11:07:                                    SYS_C003142                                                                      19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 K6-11-19:11:07:                                    USER_XML_COLUMN_NAMES                                                            19-NOV-06
         1 K6-11-19:11:07:                                    USER_XML_TAB_COLS                                                                19-NOV-06
         1 L6-11-19:10:30:                                    LOADER_PARAM_INFO                                                                19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 L6-11-19:10:30:                                    MVIEW$_ADV_CLIQUE_PK                                                             19-NOV-06
         1 L6-11-19:10:30:                                    MVIEW$_ADV_WORKLOAD_PK                                                           19-NOV-06
         1 L6-11-19:10:55:                                    /2b64edd1_InvalidName                                                            19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 L6-11-19:10:55:                                    /2f5e2b7a_TreeClassDef                                                           19-NOV-06
         1 L6-11-19:10:55:                                    /3034e0cd_AbstractSubWriter                                                      19-NOV-06
         1 L6-11-19:10:57:                                    /98b64cec_OracleErrorsText_zh_                                                   19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 L6-11-19:10:57:                                    /9966bd63_Oracle8JdbcChecker                                                     19-NOV-06
         1 L6-11-19:10:57:                                    /9abe3626_StreamDecoderConvert                                                   19-NOV-06
         1 L6-11-19:11:12:                                    /ef54c4c1_NoMarkStream                                                           19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 L6-11-19:11:12:                                    javax/media/jai/AreaOpImage                                                      19-NOV-06
         1 L6-11-19:11:12:                                    javax/media/jai/KernelJAI                                                        19-NOV-06
         1 M6-11-19:10:36:                                    SYS_YOID0000006391$                                                              19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 M6-11-19:10:37:                                    KU$_OBJGRANT_VIEW                                                                19-NOV-06
         1 M6-11-19:10:37:                                    KU$_OPARG_T                                                                      19-NOV-06
         1 M6-11-19:10:55:                                    /a7f33a81_KeyManagerFactorySpi                                                   19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 M6-11-19:10:55:                                    /a843c5bc_TreeCatch                                                              19-NOV-06
         1 M6-11-19:10:55:                                    /ac692a44_ConnectorImplStringA                                                   19-NOV-06
         1 M6-11-19:10:57:                                    oracle/i18n/text/OraCharset                                                      19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 M6-11-19:10:57:                                    oracle/jdbc/driver/DMSFactory                                                    19-NOV-06
         1 M6-11-19:10:57:                                    oracle/jdbc/util/Login                                                           19-NOV-06
         1 M6-11-19:11:28:                                    ALL$OLAPMR_DIMENSIONS                                                            19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 M6-11-19:11:28:                                    ALL_OLAP2_AWVIEWCOLS                                                             19-NOV-06
         1 M6-11-19:11:28:                                    ALL_OLAP2_CUBE_MEASURE_MAPS                                                      19-NOV-06
         1 N6-11-19:10:45:                                    DBA_HIST_METRIC_NAME                                                             19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 N6-11-19:10:45:                                    DBA_HIST_MTTR_TARGET_ADVICE                                                      19-NOV-06
         1 N6-11-19:10:45:                                    DBA_HIST_SNAP_ERROR                                                              19-NOV-06
         1 N6-11-19:10:55:                                    javax/crypto/SunJCE_j                                                            19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 N6-11-19:10:55:                                    javax/crypto/SunJCE_v                                                            19-NOV-06
         1 N6-11-19:10:55:                                    javax/naming/RefAddr                                                             19-NOV-06
         1 N6-11-19:10:58:                                    LocaleData_coll_fr_BE_EURO                                                       19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 N6-11-19:10:58:                                    LocaleData_coll_is_IS                                                            19-NOV-06
         1 N6-11-19:10:58:                                    oracle/i18n/data/lx0001a.glb                                                     19-NOV-06
         1 N6-11-19:11:38:                                    EM$CURRENT_METRICS                                                               19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 N6-11-19:11:38:                                    EMD_SCHEMA                                                                       19-NOV-06
         1 N6-11-19:11:38:                                    HOST_CONFIG_COMPARISON                                                           19-NOV-06
         1 O6-11-19:10:54:                                    /1d02b734_RTFReaderInfoDestina                                                   19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 O6-11-19:10:54:                                    /1fae271a_IterConvStatement                                                      19-NOV-06
         1 O6-11-19:10:54:                                    /22c33ee5_MediaSizeEngineering                                                   19-NOV-06
         1 O6-11-19:10:56:                                    /b040faea_AbstractDocumentElem                                                   19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 O6-11-19:10:56:                                    /b15e932f_CollectionsSynchroni                                                   19-NOV-06
         1 O6-11-19:10:56:                                    /b28cef24_JComponentEnableSeri                                                   19-NOV-06
         1 O6-11-19:11:01:                                    /c85b188a_FNContexts                                                             19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 O6-11-19:11:01:                                    /ce549c0_Sum                                                                     19-NOV-06
         1 O6-11-19:11:01:                                    /d70fe118_EventException                                                         19-NOV-06
         1 P6-11-19:10:27:                                    I_REG_SNAP2                                                                      19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 P6-11-19:10:27:                                    I_RLS_GRP                                                                        19-NOV-06
         1 P6-11-19:10:27:                                    I_SETTINGS1                                                                      19-NOV-06
         1 P6-11-19:10:54:                                    /b2f165b3_T2SStringBinder                                                        19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 P6-11-19:10:54:                                    /b360fa72_ParserExtension                                                        19-NOV-06
         1 P6-11-19:10:54:                                    /b37b129b_CopiedNullBinder                                                       19-NOV-06
         1 P6-11-19:10:56:                                    java/rmi/ConnectIOException                                                      19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 P6-11-19:10:56:                                    java/rmi/server/LogStream                                                        19-NOV-06
         1 P6-11-19:10:56:                                    java/rmi/server/RemoteStub                                                       19-NOV-06
         1 P6-11-19:11:04:                                    DM_ABN_DETAIL                                                                    19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 P6-11-19:11:04:                                    DM_CENTROIDS                                                                     19-NOV-06
         1 P6-11-19:11:04:                                    DM_ITEMSET                                                                       19-NOV-06
         1 Q6-11-19:10:29:                                    PRODUCT_COMPONENT_VERSION                                                        19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 Q6-11-19:10:29:                                    TAB                                                                              19-NOV-06
         1 Q6-11-19:10:29:                                    USER_USTATS                                                                      19-NOV-06
         1 Q6-11-19:10:54:                                    sqlj/util/SQLJParser                                                             19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 Q6-11-19:10:54:                                    sun/print/ServiceDialog$2                                                        19-NOV-06
         1 Q6-11-19:10:55:                                    /10744837_ObjectStreamClass2                                                     19-NOV-06
         1 Q6-11-19:10:57:                                    /6989c0ac_UnsafeFieldAccessorI                                                   19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 Q6-11-19:10:57:                                    /6c002de3_EchoInputStream                                                        19-NOV-06
         1 Q6-11-19:10:57:                                    /6c2f7d50_StreamEncoderCharset                                                   19-NOV-06
         1 Q6-11-19:11:11:                                    javax/media/jai/PixelAccessor                                                    19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 Q6-11-19:11:12:                                    /1013c29d_PlanarImageServerPro                                                   19-NOV-06
         1 Q6-11-19:11:12:                                    /1acd913a_IIPDescriptor                                                          19-NOV-06
         1 R6-11-19:10:34:                                    LOGMNR_KRVRDA_TEST_APPLY                                                         19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 R6-11-19:10:34:                                    OL$HINTS                                                                         19-NOV-06
         1 R6-11-19:10:34:                                    V$BACKUP_ARCHIVELOG_DETAILS                                                      19-NOV-06
         1 R6-11-19:10:55:                                    /7e87e147_DocletInvoker1                                                         19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 R6-11-19:10:55:                                    /8218bf01_ClientRequestInterce                                                   19-NOV-06
         1 R6-11-19:10:55:                                    /8236e38c_SunJCE_p                                                               19-NOV-06
         1 R6-11-19:10:57:                                    java/nio/InvalidMarkException                                                    19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 R6-11-19:10:57:                                    java/nio/channels/FileLock                                                       19-NOV-06
         1 R6-11-19:10:57:                                    java/security/GuardedObject                                                      19-NOV-06
         1 R6-11-19:11:27:                                    /9abd5a42_AWExceptionMessageRe                                                   19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 R6-11-19:11:27:                                    /b0bc945a_GenErrorDescriptionS                                                   19-NOV-06
         1 R6-11-19:11:27:                                    /c6852306_ExternalSourceExpres                                                   19-NOV-06
         1 S6-11-19:10:44:                                    DBMS_CDC_SYS_IPUBLISH                                                            19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 S6-11-19:10:44:                                    DBMS_PROPAGATION_ADM                                                             19-NOV-06
         1 S6-11-19:10:44:                                    DBMS_TSM_PRVT                                                                    19-NOV-06
         1 S6-11-19:10:55:                                    /fd763802_IIOPConnectionDelete                                                   19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 S6-11-19:10:55:                                    /fe116fe4_InvalidValue                                                           19-NOV-06
         1 S6-11-19:10:55:                                    /fefe853f_WouldReplaceExceptio                                                   19-NOV-06
         1 S6-11-19:10:58:                                    /be97e72a_DateFormatZoneData_a                                                   19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 S6-11-19:10:58:                                    /c0993064_StatementCacheCustom                                                   19-NOV-06
         1 S6-11-19:10:58:                                    /c1f701a8_rmiregistry_japroper                                                   19-NOV-06
         1 S6-11-19:11:37:                                    MGMT_E2E_JDBC_1HOUR                                                              19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 S6-11-19:11:37:                                    MGMT_JOB_STEP_LIST                                                               19-NOV-06
         1 S6-11-19:11:37:                                    MGMT_METADATA_SETS                                                               19-NOV-06
         1 T6-11-19:10:53:                                    /fcf9a3d6_AuditedProfile12Dyna                                                   19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 T6-11-19:10:53:                                    /fd945239_DTMDefaultBaseTraver                                                   19-NOV-06
         1 T6-11-19:10:53:                                    ALL_WM_CONSTRAINTS                                                               19-NOV-06
         1 T6-11-19:10:56:                                    /5f5f94c1_JPopupMenuAccessible                                                   19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 T6-11-19:10:56:                                    /62503596_MemoryCacheImageInpu                                                   19-NOV-06
         1 T6-11-19:10:56:                                    /628b38ea_StyledEditorKitUnder                                                   19-NOV-06
         1 T6-11-19:11:00:                                    javax/mail/search/HeaderTerm                                                     19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 T6-11-19:11:00:                                    javax/mail/search/OrTerm                                                         19-NOV-06
         1 T6-11-19:11:00:                                    javax/servlet/GenericServlet                                                     19-NOV-06
         1 T8-03-18:22:13:                                    WRH$_SGASTAT_U                                                                   18-MAR-08

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 T8-03-19:19:44:                                    BIN$Jf8o17FvTTyRtIEM+p/JTA==$0                                                   19-MAR-08
         1 U6-11-19:10:27:                                    APPLY$_CONF_HDLR_COLUMNS                                                         19-NOV-06
         1 U6-11-19:10:54:                                    /89925082_BasicComboBoxUIItemH                                                   19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 U6-11-19:10:54:                                    /8ace5a9d_BasicComboPopupInvoc                                                   19-NOV-06
         1 U6-11-19:10:54:                                    /8c03fffd_ConvertExpression                                                      19-NOV-06
         1 U6-11-19:10:56:                                    java/awt/Robot$1                                                                 19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 U6-11-19:10:56:                                    java/awt/Scrollbar                                                               19-NOV-06
         1 U6-11-19:10:56:                                    java/awt/Transparency                                                            19-NOV-06
         1 U6-11-19:11:03:                                    oracle/jms/AQjmsDBConnMgr                                                        19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 U6-11-19:11:03:                                    oracle/jms/AQjmsFactory                                                          19-NOV-06
         1 U6-11-19:11:03:                                    oracle/jms/AQjmsMessages_es                                                      19-NOV-06
         1 V6-11-19:10:29:                                    DBA_UPDATABLE_COLUMNS                                                            19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 V6-11-19:10:29:                                    DBA_VIEWS                                                                        19-NOV-06
         1 V6-11-19:10:29:                                    EXU81SCMU                                                                        19-NOV-06
         1 V6-11-19:10:54:                                    oracle/net/ano/AnoComm                                                           19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 V6-11-19:10:54:                                    oracle/net/aso/C04                                                               19-NOV-06
         1 V6-11-19:10:54:                                    oracle/net/nl/RepConversion                                                      19-NOV-06
         1 V6-11-19:10:57:                                    /468abe72_ExProperties                                                           19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 V6-11-19:10:57:                                    /4b20d243_SQLCheckerCustomizer                                                   19-NOV-06
         1 V6-11-19:10:57:                                    /4ed3030_SQLCheckerCustomizerE                                                   19-NOV-06
         1 V6-11-19:11:11:                                    /cd0ad5d2_SeekableInputStream                                                    19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 V6-11-19:11:11:                                    /d7ae6e21_ComplexArithmeticOpI                                                   19-NOV-06
         1 V6-11-19:11:11:                                    /dc740995_ImageCanvasPaintList                                                   19-NOV-06
         1 W6-11-19:10:33:                                    DBMS_SCHED_JOB_EXPORT                                                            19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 W6-11-19:10:33:                                    I_SCHEDULER_STEP1                                                                19-NOV-06
         1 W6-11-19:10:33:                                    I_SCHEDULER_WINDOW1                                                              19-NOV-06
         1 W6-11-19:10:55:                                    /70be5192_ManagerFactoryParame                                                   19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 W6-11-19:10:55:                                    /75197509_RemainderExpression                                                    19-NOV-06
         1 W6-11-19:10:55:                                    /7573ba94_EventContext                                                           19-NOV-06
         1 W6-11-19:10:57:                                    java/net/URLConnection$1                                                         19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 W6-11-19:10:57:                                    java/nio/CharBuffer                                                              19-NOV-06
         1 W6-11-19:10:57:                                    java/nio/DirectByteBuffer$1                                                      19-NOV-06
         1 W6-11-19:11:26:                                    SQLREMOTESPECIFICATIONUPDATENE                                                   19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 W6-11-19:11:26:                                    SQLUNMATCHEDINPUTSEXCEPTION                                                      19-NOV-06
         1 W6-11-19:11:26:                                    SQLWSTRINGSEQUENCE                                                               19-NOV-06
         1 X6-11-19:10:41:                                    _ALL_REPCOLUMN_GROUP                                                             19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 X6-11-19:10:41:                                    _USER_REPL_NESTED_TABLE_NAMES                                                    19-NOV-06
         1 X6-11-19:10:42:                                    DBMS_DEFER_ENQ_UTL_LIB                                                           19-NOV-06
         1 X6-11-19:10:55:                                    /df03598d_NoContextHelper                                                        19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 X6-11-19:10:55:                                    /e0cd4f75_ComponentFactoryBase                                                   19-NOV-06
         1 X6-11-19:10:55:                                    /e34a44ec_DelegationPermission                                                   19-NOV-06
         1 X6-11-19:10:58:                                    /4376bfbb_ProfileErrorsText_sv                                                   19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 X6-11-19:10:58:                                    /43b612aa_RuntimeRefErrorsText                                                   19-NOV-06
         1 X6-11-19:10:58:                                    /4dcd2f2c_LocaleElements_sv                                                      19-NOV-06
         1 X6-11-19:11:36:                                    /e4e2d7af_GeoRasterException                                                     19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 X6-11-19:11:36:                                    /eba1cb38_GRImageWriteParam                                                      19-NOV-06
         1 X6-11-19:11:36:                                    JAVA$POLICY$SHARED$0000000a                                                      19-NOV-06
         1 Y6-11-19:10:53:                                    /bd7edaad_TransformerIdentityI                                                   19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 Y6-11-19:10:53:                                    /be699917_NodeIterator                                                           19-NOV-06
         1 Y6-11-19:10:53:                                    /c27977a2__DynValueStub                                                          19-NOV-06
         1 Y6-11-19:10:56:                                    /4adc2f24_ThreadPolicyOperatio                                                   19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 Y6-11-19:10:56:                                    /4b3869f4_RuleBasedBreakIterat                                                   19-NOV-06
         1 Y6-11-19:10:56:                                    /4b8ed38e_PixelConverterUshort                                                   19-NOV-06
         1 Y6-11-19:11:00:                                    /7fdf8865_DataContentHandler                                                     19-NOV-06

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 Y6-11-19:11:00:                                    /a67381d0_IMAPStoreConnectionP                                                   19-NOV-06
         1 Y6-11-19:11:00:                                    /a776837d_FolderListener                                                         19-NOV-06
         1 Y7-11-07:03:42:                                    COMPOSITE_CATEGORY_TYP                                                           07-NOV-07

         I FNAME                                              SNAME                                                                            DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
         1 Y7-11-07:03:42:                                    LEAF_CATEGORY_TYP                                                                07-NOV-07
         1 Y7-11-07:03:42:                                    LINEITEM_V                                                                       07-NOV-07

299 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3241706543

---------------------------------------------------------------------------------------------
| Id  | Operation                      | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |            | 50116 |  5089K| 41585   (1)| 00:08:20 |
|*  1 |  VIEW                          |            | 50116 |  5089K| 41585   (1)| 00:08:20 |
|   2 |   WINDOW NOSORT                |            | 50116 |  2544K| 41585   (1)| 00:08:20 |
|   3 |    COUNT                       |            |       |       |            |          |
|   4 |     TABLE ACCESS BY INDEX ROWID| CUSTOMER   | 50116 |  2544K| 40930   (1)| 00:08:12 |
|*  5 |      INDEX FULL SCAN           | IDX_USE_ME | 50116 |       |   389   (1)| 00:00:05 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(MOD("RNUM",506)<3)
   5 - filter("FNAME" IS NOT NULL)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      40897  consistent gets
          0  physical reads
          0  redo size
      15840  bytes sent via SQL*Net to client
        605  bytes received via SQL*Net from client
         21  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        299  rows processed

SQL> 

[Updated on: Fri, 28 March 2008 16:52]

Report message to a moderator

Re: Bucketing Query Performance [message #309860 is a reply to message #309777] Sat, 29 March 2008 07:22 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
How fast is it now?
How fast do you hope to make it?

As with your other thread (you haven't replied to my latest offering there) you have used an index to eliminate a sort, and reduced the SQL to a single FULL SCAN of the index.

If you think Oracle can do better, I think you're being a bit unreasonable.

The only things I can think of to consider are:
- INDEX FULL SCAN reads data block-by-block. This is ineffiecient. FULL TABLE SCAN and FAST-FULL INDEX SCAN read multi-blocks making I/O much faster. But both of these would require a sort; this would steal back any performance savings.

- You are not keeping every row, so there is some redundant I/O. Can it be eliminated? I think not. I know of know way to skip-forward n rows in an index. You can write clever code to skip to the next distinct value, but the only way to count those rows is to read them.

- Once we have eliminated redundant I/O and sorts, the next option in tuning is to parallelise. Not much opportunity here. If you range-partitioned the table on FNAME you might have a shot. But the trick would be to get partitions of equal size. That would be easier with HASH partitions, but that would upset your sorting.

I think you're at the end of the road.

Ross Leishman
Re: Bucketing Query Performance [message #309865 is a reply to message #309777] Sat, 29 March 2008 07:43 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
You could add DOB to the index to eliminate the table access. That is all I can think of.
Re: Bucketing Query Performance [message #309878 is a reply to message #309777] Sat, 29 March 2008 10:35 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8623
Registered: November 2002
Location: California, USA
Senior Member
The query may not always do what you are describing. The rownum in the inner query is derived before the order by is applied, so the rownums may not always be in the same order as the order by. You need to either use three levels of nested queries, putting the rownum with rn alias in the middle query or use "row_number () over (order by fname, sname, id) rn" instead of "rownum rn".

Can you describe the purpose of this bucketing? I can't help thinking you may be doing something the hard way.


Re: Bucketing Query Performance [message #309905 is a reply to message #309860] Sat, 29 March 2008 13:45 Go to previous messageGo to next message
verpies
Messages: 28
Registered: March 2008
Location: Seattle
Junior Member
Ross,

The query takes 2 seconds to execute now, and I'd like to speed it up to at least 0.5sec.

I do not know Oracle well enough to see if I am unreasonable, however I am a seasoned Real Programmer who implemented many trees (Red&Black, B+Tree, PK-Tree, XY-AVL, Trie) in C and assembler, and it is hard for me to accept the unnecessary scans and I/O because I've done better myself with indices of all sorts (albeit not in Oracle).
I'm down to the bare metal guy, who thinks in bytes & bits and is disgusted by monstrosities like HTML, etc...

I do not understand how Oracle organizes its indices, but if there is some kind if In-Order sequencing of the index file, maybe jumping X rows ahead can be done without scanning all of the intermediate rows. The LEAD windowing function comes to mind here...

If I am at the end of the road with this problem then it is a valid answer, but it would mean that RDBMS architecture is architecturally flawed if it cannot deal with such class of problems efficiently.


Regards,
George
Re: Bucketing Query Performance [message #309906 is a reply to message #309878] Sat, 29 March 2008 13:48 Go to previous messageGo to next message
verpies
Messages: 28
Registered: March 2008
Location: Seattle
Junior Member
Barbara,

I was afraid this was wrong. I'll try the 3 levels of nested queries or the analytical row_number function.

Thanks for pointing it out.


Regards,
George

P.S.
I do not want to elaborate about the purpose of the query.

[Updated on: Sat, 29 March 2008 13:49]

Report message to a moderator

Re: Bucketing Query Performance [message #309907 is a reply to message #309906] Sat, 29 March 2008 14:09 Go to previous messageGo to next message
verpies
Messages: 28
Registered: March 2008
Location: Seattle
Junior Member
Dear All,

Experimenting with the ROW_NUMBER analytical function and a 2 level nesting of the query has brought me to an interesting observation in the query plan.
Namely to the WINDOW NOSORT STOPKEY

Intersting from perfomance point of view...

Just sharing,
George

SELECT i,FNAME,SNAME,DOB
FROM (
	SELECT /*+ INDEX_ASC(CUSTOMER IDX_USE_ME)*/
            row_number() OVER (PARTITION BY FNAME,SNAME ORDER BY FNAME,SNAME,ID) i,
            row_number() OVER (ORDER BY FNAME,SNAME,ID) rnum,
            --LEAD(FNAME, 10000, 0) OVER (ORDER BY FNAME,SNAME,ID) inorderjump,
            FNAME,SNAME,DOB
        FROM CUSTOMER
        WHERE FNAME IS NOT NULL AND SNAME IS NOT NULL         
        ORDER BY FNAME,SNAME,ID
     )
WHERE rnum=10000
Re: Bucketing Query Performance [message #309921 is a reply to message #309907] Sat, 29 March 2008 15:55 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8623
Registered: November 2002
Location: California, USA
Senior Member
On 11g I get window sort pushed rank and fast execution.

SCOTT@orcl_11g> SELECT i,FNAME,SNAME,DOB
  2  FROM (
  3  	     SELECT /*+ INDEX_ASC(CUSTOMER IDX_USE_ME)*/
  4  		 row_number() OVER (PARTITION BY FNAME,SNAME ORDER BY FNAME,SNAME,ID) i,
  5  		 row_number() OVER (ORDER BY FNAME,SNAME,ID) rnum,
  6  		 --LEAD(FNAME, 10000, 0) OVER (ORDER BY FNAME,SNAME,ID) inorderjump,
  7  		 FNAME,SNAME,DOB
  8  	     FROM CUSTOMER
  9  	     WHERE FNAME IS NOT NULL AND SNAME IS NOT NULL
 10  	     ORDER BY FNAME,SNAME,ID
 11  	  )
 12  WHERE rnum=10000
 13  /

         I FNAME           SNAME                          DOB
---------- --------------- ------------------------------ ---------
         1 C7-10-15:10:41: /4585bb77_MatchCDFDescriptor   15-OCT-07

Elapsed: 00:00:00.15

Execution Plan
----------------------------------------------------------
Plan hash value: 2990140013

-------------------------------------------------------------------------------------
| Id  | Operation                | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |          | 68419 |  6948K|   171   (1)| 00:00:03 |
|*  1 |  VIEW                    |          | 68419 |  6948K|   171   (1)| 00:00:03 |
|*  2 |   WINDOW SORT PUSHED RANK|          | 68419 |  3541K|   171   (1)| 00:00:03 |
|*  3 |    TABLE ACCESS FULL     | CUSTOMER | 68419 |  3541K|   171   (1)| 00:00:03 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("RNUM"=10000)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY "FNAME","SNAME","ID")<=10000)
   3 - filter("FNAME" IS NOT NULL)

SCOTT@orcl_11g>


From the documentation:

"Predicate Pushing

For those views that are not merged, the query transformer can push the relevant predicates from the containing query block into the view query block. This technique improves the subplan of the non-merged view, because the pushed-in predicates can be used either to access indexes or to act as filters."
Re: Bucketing Query Performance [message #309922 is a reply to message #309921] Sat, 29 March 2008 16:35 Go to previous messageGo to next message
verpies
Messages: 28
Registered: March 2008
Location: Seattle
Junior Member
Barbara,

Very interesting! Smile

..I wonder how much better it still would be if it used an index?

Could you try it with the mod(rnum,684)<=3 in the WHERE clause on 11g, and see how much it slows down?
I have only 10g to experiment with.


Regards,
George

P.S.
Of course, change the literal 684 to 1/100th of your row count.

[Updated on: Sat, 29 March 2008 16:41]

Report message to a moderator

Re: Bucketing Query Performance [message #309923 is a reply to message #309922] Sat, 29 March 2008 16:53 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8623
Registered: November 2002
Location: California, USA
Senior Member
It loses the plan.

SCOTT@orcl_11g> SELECT i,FNAME,SNAME,DOB
  2  FROM (
  3  	     SELECT /*+ INDEX_ASC(CUSTOMER IDX_USE_ME)*/
  4  		 row_number() OVER (PARTITION BY FNAME,SNAME ORDER BY FNAME,SNAME,ID) i,
  5  		 row_number() OVER (ORDER BY FNAME,SNAME,ID) rnum,
  6  		 --LEAD(FNAME, 10000, 0) OVER (ORDER BY FNAME,SNAME,ID) inorderjump,
  7  		 FNAME,SNAME,DOB
  8  	     FROM CUSTOMER
  9  	     WHERE FNAME IS NOT NULL AND SNAME IS NOT NULL
 10  	     ORDER BY FNAME,SNAME,ID
 11  	  )
 12  WHERE MOD (rnum, 684) <= 3
 13  /

         I FNAME           SNAME                          DOB
---------- --------------- ------------------------------ ---------
         1 @2-08-01:09:48: SDO_LIST_TYPE                  15-OCT-07
         1 @7-04-09:14:17: ST_ANNOTATION_TEXT             15-OCT-07
         1 @7-10-15:10:09: ALL_IND_COLUMNS                15-OCT-07
         1 @7-10-15:10:28: /9330a682_BasicToggleButtonUI  15-OCT-07
         1 @7-10-15:10:28: /94254c62_VMMethodVMMethodImpl 15-OCT-07
         1 @7-10-15:10:28: /9512e7a_AMD64AbstractMIR2LIRC 15-OCT-07
         1 @7-10-15:10:28: /9678561d_ConditionalExpressio 15-OCT-07
         1 @7-10-15:10:30: /a106cfc4_WritableRenderedImag 15-OCT-07
         1 @7-10-15:10:30: /a15b376c_NVListImpl           15-OCT-07
         1 @7-10-15:10:30: /a210f279_CopierManager        15-OCT-07
         1 @7-10-15:10:30: /a2af1500_BasicSplitPaneUIKeyb 15-OCT-07
         1 @7-10-15:10:36: SYS_C003684                    15-OCT-07
         1 @7-10-15:10:36: XDB$ELEMENT_T                  15-OCT-07
         1 @7-10-15:10:36: XDB$INCLUDE_LIST_T             15-OCT-07
         1 @7-10-15:10:36: XDB$XMLTYPE_REF_LIST_T         15-OCT-07
         1 A7-10-15:10:09: V_$SQLAREA                     15-OCT-07
         1 A7-10-15:10:09: V_$SQL_FEATURE_DEPENDENCY      15-OCT-07
         1 A7-10-15:10:09: V_$SQL_PLAN                    15-OCT-07
         1 A7-10-15:10:09: V_$THREAD                      15-OCT-07
         1 A7-10-15:10:28: /ef9b7e35_ConditionalBitPatter 15-OCT-07
         1 A7-10-15:10:28: /f143cb63_BasicProgressBarUICh 15-OCT-07
         1 A7-10-15:10:28: /f15851f5_ExecutePermissionExc 15-OCT-07
         1 A7-10-15:10:28: /f1eac0bd_IA64AbstractMIR2LIRC 15-OCT-07
         1 A7-10-15:10:30: java/awt/geom/QuadIterator     15-OCT-07
         1 A7-10-15:10:30: java/awt/image/BufferStrategy  15-OCT-07
         1 A7-10-15:10:30: java/awt/image/ConvolveOp      15-OCT-07
         1 A7-10-15:10:30: java/beans/BeanDescriptor      15-OCT-07
         1 A7-10-15:10:41: SI_SCOREBYAVGCLR               15-OCT-07
         1 A7-10-15:10:41: SI_SETTEXTUREFTR               15-OCT-07
         1 A7-10-15:10:41: SL_ATTR_T547_T                 15-OCT-07
         1 A7-10-15:10:41: XDj7qm+JEZR2uI0wGKnu+qQg==     15-OCT-07
         1 B7-10-15:10:11: REPCAT_GENERATED               15-OCT-07
         1 B7-10-15:10:11: RULE$                          15-OCT-07
         1 B7-10-15:10:11: SCHEDULER$_ATTRIB_PK           15-OCT-07
         1 B7-10-15:10:11: SCHEDULER$_EVENT_LOG           15-OCT-07
         1 B7-10-15:10:29: /25ef00e8_SecuritySupport125   15-OCT-07
         1 B7-10-15:10:29: /27bbee3d_JDWPStackFrameGetVal 15-OCT-07
         1 B7-10-15:10:29: /28b67e03_Operation            15-OCT-07
         1 B7-10-15:10:29: /28cdf563_ThreadPolicyImpl     15-OCT-07
         1 B7-10-15:10:30: sun/misc/PerformanceLogger     15-OCT-07
         1 B7-10-15:10:30: sun/misc/ProxyGenerator        15-OCT-07
         1 B7-10-15:10:30: sun/nio/ch/DatagramDispatcher  15-OCT-07
         1 B7-10-15:10:30: sun/nio/ch/SocketAdaptor$1     15-OCT-07
         1 B7-10-15:10:48: GV_OLAPI_IFACE_OP_HISTORY      15-OCT-07
         1 B7-10-15:10:48: OLAPIHANDSHAKE2                15-OCT-07
         1 B7-10-15:10:48: oracle/spatial/network/Kruskal 15-OCT-07
         1 B7-10-15:10:49: /5111681e_OraOlapConstants     15-OCT-07
         1 C7-10-15:10:13: USER_CUBE_HIER_LEVELS          15-OCT-07
         1 C7-10-15:10:13: USER_MEASURE_FOLDER_CONTENTS   15-OCT-07
         1 C7-10-15:10:13: USER_QUEUE_SCHEDULES           15-OCT-07
         1 C7-10-15:10:13: USER_SCHEDULER_CHAIN_STEPS     15-OCT-07
         1 C7-10-15:10:29: /718f419e_Constants            15-OCT-07
         1 C7-10-15:10:29: /72285d3a_DatatypeException    15-OCT-07
         1 C7-10-15:10:29: /72b13abc_BasicParserConfigura 15-OCT-07
         1 C7-10-15:10:29: /75db13f6_JDWPEventRequestSetM 15-OCT-07
         1 C7-10-15:10:31: /96ca5e42_Messages_arpropertie 15-OCT-07
         1 C7-10-15:10:31: /98b64cec_OracleErrorsText_zh_ 15-OCT-07
         1 C7-10-15:10:31: /9966bd63_Oracle8JdbcChecker   15-OCT-07
         1 C7-10-15:10:31: /9a3789ab_InvalidRelationIdExc 15-OCT-07
         1 C7-10-15:10:56: WK$INST_ADMIN_LIST             15-OCT-07
         1 C7-10-15:10:56: WK$PT_ID_SEQ                   15-OCT-07
         1 C7-10-15:10:56: WK$SCHED_ID_SEQ                15-OCT-07
         1 C7-10-15:10:56: WK$TRACE_PK                    15-OCT-07
         1 D7-10-15:10:23: OWA_OPT_LOCK                   15-OCT-07
         1 D7-10-15:10:23: SYS_IOT_OVER_11848             15-OCT-07
         1 D7-10-15:10:26: ALL_WORKSPACES                 15-OCT-07
         1 D7-10-15:10:26: OWM_ASSERT_PKG                 15-OCT-07
         1 D7-10-15:10:29: /cba799a5_TransportServiceList 15-OCT-07
         1 D7-10-15:10:29: /cc33bb15_ResourceManager      15-OCT-07
         1 D7-10-15:10:29: /cc620e87_IAND                 15-OCT-07
         1 D7-10-15:10:29: /ccce3e04_RMIConnectorRMIClien 15-OCT-07
         1 D7-10-15:10:31: lib/security/java.security     15-OCT-07
         1 D7-10-15:10:31: oracle/aurora/rdbms/JMXAgent   15-OCT-07
         1 D7-10-15:10:31: oracle/aurora/util/JRIExt      15-OCT-07
         1 D7-10-15:10:31: oracle/aurora/util/TableReader 15-OCT-07
         1 D7-10-15:10:59: MGMT_ECM_SNAPSHOT_PKG          15-OCT-07
         1 D7-10-15:11:00: AQ$_MGMT_PAF_MSG_QTABLE_1_I    15-OCT-07
         1 D7-10-15:11:00: CHECK_DUPLICATE_TARGETS        15-OCT-07
         1 D7-10-15:11:00: EM_LICENSE                     15-OCT-07
         1 E7-10-15:10:28: /24c93e10_IA64AbstractMIR2LIRC 15-OCT-07
         1 E7-10-15:10:28: /255eb681_AnnotationValueImplT 15-OCT-07
         1 E7-10-15:10:28: /25bcb906_ConditionalBitPatter 15-OCT-07
         1 E7-10-15:10:28: /25ce416e_OracleClassLoader    15-OCT-07
         1 E7-10-15:10:29: sun/misc/CacheEntry            15-OCT-07
         1 E7-10-15:10:29: sun/net/www/HeaderParser       15-OCT-07
         1 E7-10-15:10:29: sun/security/jgss/ProviderList 15-OCT-07
         1 E7-10-15:10:29: sun/security/krb5/Checksum     15-OCT-07
         1 E7-10-15:10:33: /1eb71508_XSLJDWPSocketConn    15-OCT-07
         1 E7-10-15:10:33: /2293736_XSLTransformerErrorLi 15-OCT-07
         1 E7-10-15:10:33: /29988fbd_MailcapCommandMap    15-OCT-07
         1 E7-10-15:10:33: /2dfbd72c_ComparisonTerm       15-OCT-07
         1 E7-11-08:05:58: AQ$ORDERS_QUEUETABLE_R         08-NOV-07
         1 E7-11-08:05:58: AQ$_STREAMS_QUEUE_TABLE_H      08-NOV-07
         1 E7-11-08:05:58: COSTS                          08-NOV-07
         1 E7-11-08:05:58: COSTS_PROD_BIX                 08-NOV-07
         1 F7-10-15:10:28: /7df2be4b_BasicComboBoxUI1     15-OCT-07
         1 F7-10-15:10:28: /7ef71be3_VMWellKnownMethodReg 15-OCT-07
         1 F7-10-15:10:28: /7f9d6b06_PowerPCCodeGenerator 15-OCT-07
         1 F7-10-15:10:28: /8136aaa1_ClassReader1         15-OCT-07
         1 F7-10-15:10:30: /846552c0_BaseMetaDataImpl     15-OCT-07
         1 F7-10-15:10:30: /8573778c_WriteContents        15-OCT-07
         1 F7-10-15:10:30: /85db04e1_AttributeNotFoundExc 15-OCT-07
         1 F7-10-15:10:30: /87f55b2d_ImageViewImageLabelV 15-OCT-07
         1 F7-10-15:10:34: oracle/xquery/exec/LogicalOp   15-OCT-07
         1 F7-10-15:10:34: oracle/xquery/exec/PathAxis    15-OCT-07
         1 F7-10-15:10:34: oracle/xquery/func/OraVersion  15-OCT-07
         1 F7-10-15:10:34: oracle/xquery/parser/Qname     15-OCT-07
         1 G7-10-15:10:09: PROXY_ROLE_DATA$               15-OCT-07
         1 G7-10-15:10:09: RESOURCE_MAPPING_PRIORITY$     15-OCT-07
         1 G7-10-15:10:09: RLS_SC$                        15-OCT-07
         1 G7-10-15:10:09: RULESET$                       15-OCT-07
         1 G7-10-15:10:28: /e2cd7a5d_Dominators           15-OCT-07
         1 G7-10-15:10:28: /e2d98d5_IA64BaseLIRInstrFSABu 15-OCT-07
         1 G7-10-15:10:28: /e2ffd5f2_BasicTreeUITreeCance 15-OCT-07
         1 G7-10-15:10:28: /e505feef_AdaptorBootstrapDefa 15-OCT-07
         1 G7-10-15:10:30: /e6ed02b7_VetoableChangeSuppor 15-OCT-07
         1 G7-10-15:10:30: /e7f02dc5__CodeBaseStub        15-OCT-07
         1 G7-10-15:10:30: /e8c9db7b_CDRInputStream_1_0   15-OCT-07
         1 G7-10-15:10:30: /e99d1147_DocumentName         15-OCT-07
         1 G7-10-15:10:41: /a0d62d0d_FileLoadDescriptor   15-OCT-07
         1 G7-10-15:10:41: /a28b5b32_strhChunk            15-OCT-07
         1 G7-10-15:10:41: /a500e316_JaiI18N              15-OCT-07
         1 G7-10-15:10:41: /a8cafc03_MlibTranslateRIF     15-OCT-07
         1 H7-10-15:10:11: ALL_REPGENOBJECTS              15-OCT-07
         1 H7-10-15:10:11: ALL_REPGROUP                   15-OCT-07
         1 H7-10-15:10:11: ALL_RULE_SETS                  15-OCT-07
         1 H7-10-15:10:11: AQ$_SUBSCRIBER_TABLE_PRIMARY   15-OCT-07
         1 H7-10-15:10:28: sqlj/util/Parselet             15-OCT-07
         1 H7-10-15:10:28: sun/swing/FilePane$3           15-OCT-07
         1 H7-10-15:10:28: sun/text/IntHashtable          15-OCT-07
         1 H7-10-15:10:28: sun/tools/asm/CatchData        15-OCT-07
         1 H7-10-15:10:30: javax/swing/text/html/HTML     15-OCT-07
         1 H7-10-15:10:30: javax/swing/tree/RowMapper     15-OCT-07
         1 H7-10-15:10:30: javax/swing/undo/CompoundEdit  15-OCT-07
         1 H7-10-15:10:30: oracle/aurora/vm/IUHandle      15-OCT-07
         1 H7-10-15:10:44: ST_GEOMCOLLECTION              15-OCT-07
         1 H7-10-15:10:45: COORD_REF_SYSTEM_PRIM          15-OCT-07
         1 H7-10-15:10:45: SDO_ADMIN                      15-OCT-07
         1 H7-10-15:10:45: SDO_COORD_OPS                  15-OCT-07
         1 I7-10-15:10:12: LOGMNR_TAB_COLS_SUPPORT        15-OCT-07
         1 I7-10-15:10:12: ODCIINDEXINFODUMP              15-OCT-07
         1 I7-10-15:10:12: ODCIOBJECTLIST                 15-OCT-07
         1 I7-10-15:10:12: REPCAT$_SITES_NEW_FK2_IDX      15-OCT-07
         1 I7-10-15:10:29: /5cd64240_MessageTokenMessageT 15-OCT-07
         1 I7-10-15:10:29: /5f1659ed_RealType             15-OCT-07
         1 I7-10-15:10:29: /60f1dbe_XMLEntityManagerEntit 15-OCT-07
         1 I7-10-15:10:29: /61027b31_VoidType             15-OCT-07
         1 I7-10-15:10:31: /561f4976_AbstractPreferencesN 15-OCT-07
         1 I7-10-15:10:31: /564607d_HTMLAnchorElement     15-OCT-07
         1 I7-10-15:10:31: /5aaea7ab_OraCustomizerErrorsT 15-OCT-07
         1 I7-10-15:10:31: /5ae161b6_DefaultProxySelector 15-OCT-07
         1 I7-10-15:10:54: USER_SDO_NETWORK_USER_DATA     15-OCT-07
         1 I7-10-15:10:55: ORDMD_TNPC_LIBS                15-OCT-07
         1 I7-10-15:10:55: R_TABLE                        15-OCT-07
         1 I7-10-15:10:55: SDO_WFS_LOCK                   15-OCT-07
         1 J7-10-15:10:17: DBMS_EXTENDED_TTS_CHECKS       15-OCT-07
         1 J7-10-15:10:17: DBMS_METADATA_UTIL             15-OCT-07
         1 J7-10-15:10:17: DBMS_SUMVDM                    15-OCT-07
         1 J7-10-15:10:17: WRI$_ADV_OBJSPACE_TREND_T      15-OCT-07
         1 J7-10-15:10:29: /b7fde19a_DefaultCallbackHandl 15-OCT-07
         1 J7-10-15:10:29: /b86f7d4a_AptAptTreeScanner    15-OCT-07
         1 J7-10-15:10:29: /b93aef44_JDWPStackFrameSetVal 15-OCT-07
         1 J7-10-15:10:29: /b960f153_LdapSchemaCtx        15-OCT-07
         1 J7-10-15:10:31: /f8866ac7_SslRMIServerSocketFa 15-OCT-07
         1 J7-10-15:10:31: /fa7e56d4_BasicToolBarSeparato 15-OCT-07
         1 J7-10-15:10:31: /fab81813_MetalComboBoxUI      15-OCT-07
         1 J7-10-15:10:31: /fc374dcc_ProviderList3        15-OCT-07
         1 J7-10-15:10:57: MGMT_USER_TARGETS_PK           15-OCT-07
         1 J7-10-15:10:57: MGMT_VIOLATION_CONTEXT_PK      15-OCT-07
         1 J7-10-15:10:57: PARAM_VALUES_TAB               15-OCT-07
         1 J7-10-15:10:57: PK_MGMT_HTTP_SESS_CBS          15-OCT-07
         1 K7-10-15:10:27: sun/nio/cs/ext/ISCII91         15-OCT-07
         1 K7-10-15:10:27: sun/nio/cs/ext/JISAutoDetect   15-OCT-07
         1 K7-10-15:10:27: sun/nio/cs/ext/MS1255$Encoder  15-OCT-07
         1 K7-10-15:10:27: sun/nio/cs/ext/MS874$Encoder   15-OCT-07
         1 K7-10-15:10:29: /f8d8eba_LocalClientRequestDis 15-OCT-07
         1 K7-10-15:10:29: /fc898616_XMLEntityManagerScan 15-OCT-07
         1 K7-10-15:10:29: /fdb344c_AttributeSetMethodGen 15-OCT-07
         1 K7-10-15:10:29: /fe43c70e_InvalidName          15-OCT-07
         1 K7-10-15:10:31: sun/io/CharToByteCp922         15-OCT-07
         1 K7-10-15:10:31: sun/io/CharToByteCp950         15-OCT-07
         1 K7-10-15:10:31: sun/io/CharToByteISO2022       15-OCT-07
         1 K7-10-15:10:31: sun/io/CharToByteISO8859_9     15-OCT-07
         1 K7-10-15:11:07: WWV_FLOW_TRANSLATABLE_COLS$    15-OCT-07
         1 K7-10-15:11:07: WWV_FLOW_WS_MAP_IDX2           15-OCT-07
         1 K7-10-15:11:07: WWV_MIG_ACC_FORM_MDL_IDX2      15-OCT-07
         1 K7-10-15:11:07: WWV_MIG_ACC_GROUPS             15-OCT-07
         1 L7-10-15:10:28: /63a4e300_BinaryConstantPool   15-OCT-07
         1 L7-10-15:10:28: /63a8cb00_BoundsCheckRemovalSu 15-OCT-07
         1 L7-10-15:10:28: /6526faab_StructMetaData       15-OCT-07
         1 L7-10-15:10:28: /6653a3ee_BasicInternalFrameTi 15-OCT-07
         1 L7-10-15:10:30: /67174ddd_HashtableEmptyEnumer 15-OCT-07
         1 L7-10-15:10:30: /68c8d9b9_WeakHashMapValueIter 15-OCT-07
         1 L7-10-15:10:30: /6989c0ac_UnsafeFieldAccessorI 15-OCT-07
         1 L7-10-15:10:30: /6aa6faf_StringCodingConverter 15-OCT-07
         1 L7-10-15:10:34: /e4907c47_OraURIException      15-OCT-07
         1 L7-10-15:10:34: /e6092e7b_XQueryXUtils         15-OCT-07
         1 L7-10-15:10:34: /f0c06870_XSLJDWPSockReader    15-OCT-07
         1 L7-10-15:10:34: /f29b75a6_ExpectedSequenceType 15-OCT-07
         1 M7-10-15:10:09: GV$TEMP_CACHE_TRANSFER         15-OCT-07
         1 M7-10-15:10:09: GV_$ASM_FILE                   15-OCT-07
         1 M7-10-15:10:09: GV_$AW_OLAP                    15-OCT-07
         1 M7-10-15:10:09: GV_$BUFFERED_QUEUES            15-OCT-07
         1 M7-10-15:10:28: /c3159ec_OracleResultSetMetaDa 15-OCT-07
         1 M7-10-15:10:28: /c4cda70e_CommonSourceObject   15-OCT-07
         1 M7-10-15:10:28: /c5790956_VerifierClassLoader  15-OCT-07
         1 M7-10-15:10:28: /c591b98e_ByteCodeToMIRInstrMI 15-OCT-07
         1 M7-10-15:10:30: /c6a2699c_AccessibleKeyBinding 15-OCT-07
         1 M7-10-15:10:30: /c7e340d5_FontRenderContext    15-OCT-07
         1 M7-10-15:10:30: /c80d76bd_MissingFormatArgumen 15-OCT-07
         1 M7-10-15:10:30: /c8dc965d_JFormattedTextFieldA 15-OCT-07
         1 M7-10-15:10:40: TiffIfd318_T                   15-OCT-07
         1 M7-10-15:10:40: XS$CACHE_ACTIONS               15-OCT-07
         1 M7-10-15:10:40: gpsStatusType265_T             15-OCT-07
         1 M7-10-15:10:41: /1106a6c1_MultiplyOpImage      15-OCT-07
         1 N7-10-15:10:10: DICTIONARY                     15-OCT-07
         1 N7-10-15:10:10: EXU81INDC                      15-OCT-07
         1 N7-10-15:10:10: EXU81IXCP                      15-OCT-07
         1 N7-10-15:10:10: EXU81SRTU                      15-OCT-07
         1 N7-10-15:10:28: javax/sql/RowSetEvent          15-OCT-07
         1 N7-10-15:10:28: javax/swing/Spring$1           15-OCT-07
         1 N7-10-15:10:28: oracle/aurora/ncomp/tree/Node  15-OCT-07
         1 N7-10-15:10:28: oracle/aurora/rdbms/Compiler$2 15-OCT-07
         1 N7-10-15:10:30: java/util/regex/Pattern$SliceU 15-OCT-07
         1 N7-10-15:10:30: java/util/zip/Checksum         15-OCT-07
         1 N7-10-15:10:30: java/util/zip/GZIPInputStream  15-OCT-07
         1 N7-10-15:10:30: java/util/zip/ZipFile$1        15-OCT-07
         1 N7-10-15:10:42: /68d868b4_TIFFFaxEncoder       15-OCT-07
         1 N7-10-15:10:42: /6b1383f9_MlibExpOpImage       15-OCT-07
         1 N7-10-15:10:42: /6d546325_InterpolationBicubic 15-OCT-07
         1 N7-10-15:10:42: /75b6e045_DicomXSLTTreeLeaf    15-OCT-07
         1 O7-10-15:10:11: WRI$_SQLSET_REFERENCES         15-OCT-07
         1 O7-10-15:10:11: WRI$_TRACING_IND1              15-OCT-07
         1 O7-10-15:10:11: WRM$_DATABASE_INSTANCE_PK      15-OCT-07
         1 O7-10-15:10:11: WRR$_CONNECTION_MAP_PK         15-OCT-07
         1 O7-10-15:10:29: /374ed299_ParserTable9         15-OCT-07
         1 O7-10-15:10:29: /37eb2758_SWAP                 15-OCT-07
         1 O7-10-15:10:29: /383696a5_Choose               15-OCT-07
         1 O7-10-15:10:29: /394708f4_SortResponseControl  15-OCT-07
         1 O7-10-15:10:31: /3571c81c_DateFormatZoneData_s 15-OCT-07
         1 O7-10-15:10:31: /39c45e79_ReturnType           15-OCT-07
         1 O7-10-15:10:31: /3b25c3cc_Java                 15-OCT-07
         1 O7-10-15:10:31: /3cb23c9e_ClassResolver        15-OCT-07
         1 O7-10-15:10:49: CWM2$LEVELATTRIBUTEMAP         15-OCT-07
         1 O7-10-15:10:49: CWM2$LEVELATTRIBUTE_PK         15-OCT-07
         1 O7-10-15:10:49: CWM2$MEASUREUPD                15-OCT-07
         1 O7-10-15:10:49: CWM2$MRALL_DIM_LEVEL_ATTR_MAPS 15-OCT-07
         1 P7-10-15:10:15: KUPC$_MASTER_MSG               15-OCT-07
         1 P7-10-15:10:15: KUPC$_PAR_CON                  15-OCT-07
         1 P7-10-15:10:15: LOGSTDBY$EVENTS                15-OCT-07
         1 P7-10-15:10:15: LOGSTDBY_UNSUPPORT_TAB_11_1    15-OCT-07
         1 P7-10-15:10:29: /86a42226_ArrayNotificationBuf 15-OCT-07
         1 P7-10-15:10:29: /86c5292e_TypesHashCodeFcn     15-OCT-07
         1 P7-10-15:10:29: /887375b1_ClientNotifForwarder 15-OCT-07
         1 P7-10-15:10:29: /88aa9821_ConstFold            15-OCT-07
         1 P7-10-15:10:31: /d8a72f85_MultiMenuBarUI       15-OCT-07
         1 P7-10-15:10:31: /d8ae555b_KeyGeneratorCoreARCF 15-OCT-07
         1 P7-10-15:10:31: /d9bc5577_LocaleElements_ar_TN 15-OCT-07
         1 P7-10-15:10:31: /db58ada5_RowSetResourceBundle 15-OCT-07
         1 P7-10-15:10:57: MGMT_HC_CPU_DETAILS_IDX        15-OCT-07
         1 P7-10-15:10:57: MGMT_HC_VENDOR_SW_SUMMARY      15-OCT-07
         1 P7-10-15:10:57: MGMT_INV_PATCHSET              15-OCT-07
         1 P7-10-15:10:57: MGMT_INV_SUMMARY_IDX           15-OCT-07
         1 Q7-10-15:10:27: /e8494560_PermissionNameMenuLi 15-OCT-07
         1 Q7-10-15:10:27: /ed4eba72_ReverseState         15-OCT-07
         1 Q7-10-15:10:27: /f0698992_RemoteMonitoredVmNot 15-OCT-07
         1 Q7-10-15:10:27: JAVA$JVM$STEPS$DONE            15-OCT-07
         1 Q7-10-15:10:29: /e3b46633_SunJCE_ag            15-OCT-07
         1 Q7-10-15:10:29: /e3c2b1ce_INSURLHandler        15-OCT-07
         1 Q7-10-15:10:29: /e4390c99_EventSetImplExceptio 15-OCT-07
         1 Q7-10-15:10:29: /e4af2d78_CastExpr             15-OCT-07
         1 Q7-10-15:10:31: org/omg/CORBA/VM_CUSTOM        15-OCT-07
         1 Q7-10-15:10:31: org/omg/IOP/TAG_INTERNET_IOP   15-OCT-07
         1 Q7-10-15:10:31: org/w3c/dom/TypeInfo           15-OCT-07
         1 Q7-10-15:10:31: org/xml/sax/ext/Locator2Impl   15-OCT-07
         1 Q7-10-15:11:07: WWV_BD_FLOWPROCESSING          15-OCT-07
         1 Q7-10-15:11:07: WWV_BIU_FLOWFLASHCHARTSERIES   15-OCT-07
         1 Q7-10-15:11:07: WWV_BIU_FLOW_DB_AUTH           15-OCT-07
         1 Q7-10-15:11:07: WWV_BIU_FLOW_PLATFORM_PREFS    15-OCT-07
         1 R7-10-15:10:28: /432a0499_ASTVariableInitializ 15-OCT-07
         1 R7-10-15:10:28: /43e0e39c_LoopOptimizations5   15-OCT-07
         1 R7-10-15:10:28: /4548ec8d_AMD64AbstractMIR2LIR 15-OCT-07
         1 R7-10-15:10:28: /45b38554_SnmpCachedData1      15-OCT-07
         1 R7-10-15:10:30: /3551b5d0_JLabelAccessibleJLab 15-OCT-07
         1 R7-10-15:10:30: /35e884ee_JTableNumberEditor   15-OCT-07
         1 R7-10-15:10:30: /370ef07d_ServerSocketChannelI 15-OCT-07
         1 R7-10-15:10:30: /37af531e_SystemEventQueueUtil 15-OCT-07
         1 R7-10-15:10:33: oracle/xdb/dom/XDBDocumentType 15-OCT-07
         1 R7-10-15:10:33: oracle/xdb/servlet/XDBStream   15-OCT-07
         1 R7-10-15:10:33: oracle/xml/async/DOMBuilder    15-OCT-07
         1 R7-10-15:10:33: oracle/xml/binxml/BinXMLStream 15-OCT-07
         1 S2-10-23:15:30: AQ$_JMS_MESSAGE_PROPERTY       15-OCT-07
         1 S7-04-12:12:59: AQ$_RECIPIENTS                 15-OCT-07
         1 S7-04-12:12:59: RE$NV_NODE                     15-OCT-07
         1 S7-10-15:10:09: ALL_COL_COMMENTS               15-OCT-07
         1 S7-10-15:10:28: /a224f89a_MIRThrow             15-OCT-07
         1 S7-10-15:10:28: /a262318d_MetalBordersInternal 15-OCT-07
         1 S7-10-15:10:28: /a3aa743b_RebindingBatchParser 15-OCT-07
         1 S7-10-15:10:28: /a6497024_OracleTimeoutThreadP 15-OCT-07
         1 S7-10-15:10:30: /9a52cc97_LayeredHighlighter   15-OCT-07
         1 S7-10-15:10:30: /9b14d1c3_BlitBgTraceBlitBg    15-OCT-07
         1 S7-10-15:10:30: /9b35b28b_ValueHandlerImpl1    15-OCT-07
         1 S7-10-15:10:30: /9b679ecb_ServiceDialogPageSet 15-OCT-07
         1 S7-10-15:10:36: XDB$SIMPLECONT_RES_T           15-OCT-07
         1 S7-10-15:10:36: XMLCHARACTEROUTPUTSTREAM       15-OCT-07
         1 S7-10-15:10:37: ALL_XML_INDEXES                15-OCT-07
         1 S7-10-15:10:37: DBA_XML_TAB_COLS               15-OCT-07
         1 T7-10-15:10:09: V_$JAVA_LIBRARY_CACHE_MEMORY   15-OCT-07
         1 T7-10-15:10:09: V_$LOCK_TYPE                   15-OCT-07
         1 T7-10-15:10:09: V_$PERSISTENT_QUEUES           15-OCT-07
         1 T7-10-15:10:09: V_$PX_PROCESS                  15-OCT-07
         1 T7-10-15:10:28: /f292305d_OracleConversionRead 15-OCT-07
         1 T7-10-15:10:28: /f3d144e_MkMsg                 15-OCT-07
         1 T7-10-15:10:28: /f461ba6f_ClassDocImpl         15-OCT-07
         1 T7-10-15:10:28: /f596ba05_UnpickleContext      15-OCT-07
         1 T7-10-15:10:30: java/awt/font/NumericShaper    15-OCT-07
         1 T7-10-15:10:30: java/awt/font/TextLine$4       15-OCT-07
         1 T7-10-15:10:30: java/awt/geom/Point2D$Double   15-OCT-07
         1 T7-10-15:10:30: java/awt/image/BufferedImage   15-OCT-07
         1 T7-10-15:10:41: SI_COLOR                       15-OCT-07
         1 T7-10-15:10:41: SI_COLORHISTOGRAM              15-OCT-07
         1 T7-10-15:10:41: SI_GETCONTENTLNGTH             15-OCT-07
         1 T7-10-15:10:41: SI_GETPSTNLCLRFTR              15-OCT-07
         1 U7-10-15:10:11: REPCAT$_TEMPLATE_OBJECTS_S     15-OCT-07
         1 U7-10-15:10:11: REPCAT$_USER_AUTHORIZATIONS_U1 15-OCT-07
         1 U7-10-15:10:11: SCHEDULER$_PROGRAM_PK          15-OCT-07
         1 U7-10-15:10:11: SCHEDULER$_RULE                15-OCT-07
         1 U7-10-15:10:29: /1e0126be_Pattern              15-OCT-07
         1 U7-10-15:10:29: /1e721728_MicToken_v2          15-OCT-07
         1 U7-10-15:10:29: /1ecde73_Main14                15-OCT-07
         1 U7-10-15:10:29: /1f354afc_XSSimpleTypeDeclXSFa 15-OCT-07
         1 U7-10-15:10:30: sun/print/PSStreamPrintJob     15-OCT-07
         1 U7-10-15:10:30: sun/print/PageableDoc          15-OCT-07
         1 U7-10-15:10:30: sun/print/ServiceDialog$4      15-OCT-07
         1 U7-10-15:10:30: sun/reflect/UTF8               15-OCT-07
         1 U7-10-15:10:48: GENDATAPROVIDERINTERFACE       15-OCT-07
         1 U7-10-15:10:48: OLAPIHANDSHAKE2                15-OCT-07
         1 U7-10-15:10:48: OLAPISHUTDOWNTRIGGER           15-OCT-07
         1 U7-10-15:10:48: OLAPLEVELTUPLES                15-OCT-07
         1 V7-10-15:10:13: USER_SCHEDULER_CHAIN_RULES     15-OCT-07
         1 V7-10-15:10:13: _DBA_QUEUE_SCHEDULES           15-OCT-07
         1 V7-10-15:10:14: KU$_10_1_SYSGRANT_VIEW         15-OCT-07
         1 V7-10-15:10:14: KU$_HTSPART_BYTES_ALLOC_VIEW   15-OCT-07
         1 V7-10-15:10:29: /763d9f4_StringBufferPool      15-OCT-07
         1 V7-10-15:10:29: /765f219d_TreeTypeTest         15-OCT-07
         1 V7-10-15:10:29: /76fb91e5_SecuritySupport126   15-OCT-07
         1 V7-10-15:10:29: /785e28d4_JPEGImageEncoderImpl 15-OCT-07
         1 V7-10-15:10:31: /9130791a_Messages_nl          15-OCT-07
         1 V7-10-15:10:31: /94b047ba_ProfileErrorsText_da 15-OCT-07
         1 V7-10-15:10:31: /974c0aa9_MirroredTypesExcepti 15-OCT-07
         1 V7-10-15:10:31: /977cf88f_SemanticErrorsText_r 15-OCT-07
         1 V7-10-15:10:56: WK$_DATA_SOURCE_TYPE           15-OCT-07
         1 V7-10-15:10:56: WK$_FSEARCH_ATTR_PK            15-OCT-07
         1 V7-10-15:10:56: WKDS_ADM                       15-OCT-07
         1 V7-10-15:10:57: AQ$_MGMT_TASK_QTABLE_T         15-OCT-07
         1 W7-10-15:10:26: DBA_WORKSPACE_SAVEPOINTS       15-OCT-07
         1 W7-10-15:10:26: OWM_REPUTIL                    15-OCT-07
         1 W7-10-15:10:26: SYS_C003350                    15-OCT-07
         1 W7-10-15:10:26: SYS_C003469                    15-OCT-07
         1 W7-10-15:10:29: /d6d4f8cf_HmacCoreHmacSHA512   15-OCT-07
         1 W7-10-15:10:29: /d6fdcf7e_NamingContextHelper  15-OCT-07
         1 W7-10-15:10:29: /d804fd61_B64Encoder           15-OCT-07
         1 W7-10-15:10:29: /d8386f82_TreeScanner          15-OCT-07
         1 W7-10-15:10:31: oracle/i18n/data/lx00011.glb   15-OCT-07
         1 W7-10-15:10:31: oracle/i18n/data/lx0002b.glb   15-OCT-07
         1 W7-10-15:10:31: oracle/i18n/data/lx0boot.glb   15-OCT-07
         1 W7-10-15:10:31: oracle/i18n/data/lx10018.glb   15-OCT-07
         1 W7-10-15:11:00: METRICS_INSERT_TRIGGER         15-OCT-07
         1 W7-10-15:11:00: MGMT$DB_INIT_PARAMS_ALL        15-OCT-07
         1 W7-10-15:11:00: MGMT$E2E_HOURLY                15-OCT-07
         1 W7-10-15:11:00: MGMT$HA_BACKUP                 15-OCT-07
         1 X7-10-15:10:28: /2cfc8d65_SynthTextAreaUI      15-OCT-07
         1 X7-10-15:10:28: /308433c9_IndentPrintWriter    15-OCT-07
         1 X7-10-15:10:28: /308fbfa1_BeanContextServices  15-OCT-07
         1 X7-10-15:10:28: /32964284_DefaultLoader        15-OCT-07
         1 X7-10-15:10:30: /122fb53c_FontConfigurationPro 15-OCT-07
         1 X7-10-15:10:30: /12e3f18d_SecureRandom         15-OCT-07
         1 X7-10-15:10:30: /13257198_AccessibleHTMLProper 15-OCT-07
         1 X7-10-15:10:30: /143bd6b4_AttributeMode        15-OCT-07
         1 X7-10-15:10:33: /459fdef3_InstanceOfExpr       15-OCT-07
         1 X7-10-15:10:33: /4d25c390_xdk_version_111060_p 15-OCT-07
         1 X7-10-15:10:33: /4e5beaa7_JXTransformer1       15-OCT-07
         1 X7-10-15:10:33: /4f88f54_XPathFollowingAxis    15-OCT-07
         1 X7-11-08:05:58: SALES_CUST_BIX                 08-NOV-07
         1 X7-11-08:05:58: SALES_PROD_BIX                 08-NOV-07
         1 X7-11-08:05:58: SALES_PROMO_BIX                08-NOV-07
         1 X7-11-08:05:58: SALES_TIME_BIX                 08-NOV-07
         1 Y7-10-15:10:28: /75730318_ParsedSynthStyleDele 15-OCT-07
         1 Y7-10-15:10:28: /75d2b0ba_KnownOptions5        15-OCT-07
         1 Y7-10-15:10:28: /75de5dcf_ActivationGroupImplA 15-OCT-07
         1 Y7-10-15:10:28: /7647e11d_BinaryClass          15-OCT-07
         1 Y7-10-15:10:30: /835eb71b_JDesktopPaneAccessib 15-OCT-07
         1 Y7-10-15:10:30: /858cce89_WrappedPlainViewWrap 15-OCT-07
         1 Y7-10-15:10:30: /86e7752b_IIOReadProgressListe 15-OCT-07
         1 Y7-10-15:10:30: /883fe97c_InputMethodListener  15-OCT-07
         1 Y7-10-15:10:35: ALL_EXPFIL_ASET_FUNCTIONS      15-OCT-07
         1 Y7-10-15:10:35: ALL_EXPFIL_EXPRSET_STATS       15-OCT-07
         1 Y7-10-15:10:35: CATINDEXMETHODS                15-OCT-07
         1 Y7-10-15:10:35: CTX_PARAMETERS                 15-OCT-07

399 rows selected.

Elapsed: 00:00:00.81

Execution Plan
----------------------------------------------------------
Plan hash value: 659629041

--------------------------------------------------------------------------------
| Id  | Operation           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |          | 68319 |  6938K|   171   (1)| 00:00:03 |
|*  1 |  VIEW               |          | 68319 |  6938K|   171   (1)| 00:00:03 |
|   2 |   WINDOW SORT       |          | 68319 |  3536K|   171   (1)| 00:00:03 |
|*  3 |    TABLE ACCESS FULL| CUSTOMER | 68319 |  3536K|   171   (1)| 00:00:03 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(MOD("RNUM",684)<=3)
   3 - filter("FNAME" IS NOT NULL)

SCOTT@orcl_11g> 

Re: Bucketing Query Performance [message #309924 is a reply to message #309865] Sat, 29 March 2008 18:24 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
coleing wrote on Sat, 29 March 2008 23:43
You could add DOB to the index to eliminate the table access. That is all I can think of.


Oh, man, I must be getting old. I didn't see the table access.

Yes, do this. You will almost certainly get your 4-fold improvement.

Also worth noting: your 2 second response time is on cached data
          0  db block gets
      40897  consistent gets
          0  physical reads
Performance will drop when this is run cold.

This does not stop the redundant data read on the index (although there is TONS of redundant data that will be no longer read if you use coleing's suggestion. I believe with some confidence that eliminating the redundant scan of discarded index rows is impossible. Oracle stores variable-width index entries. The only way to know how many entires are stored in a block is to read the block - and then it's too late.

For more information on the structure of Indexes in Oracle, and for some explanation of why the TABLE ACCESS must be eliminated, see this article.

Ross Leishman
Re: Bucketing Query Performance [message #309928 is a reply to message #309922] Sat, 29 March 2008 23:21 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8623
Registered: November 2002
Location: California, USA
Senior Member
I had an invalid test environment. I had an index named customer instead of idx_use_me, so the hint was invalid. Here are the revised results after changing the name of the index to idx_use_me.

SCOTT@orcl_11g> COLUMN fname FORMAT A15 WORD_WRAPPED
SCOTT@orcl_11g> COLUMN sname FORMAT A30 WORD_WRAPPED
SCOTT@orcl_11g> SELECT i,FNAME,SNAME,DOB
  2  FROM (
  3  	     SELECT /*+ INDEX_ASC(CUSTOMER IDX_USE_ME)*/
  4  		 row_number() OVER (PARTITION BY FNAME,SNAME ORDER BY FNAME,SNAME,ID) i,
  5  		 row_number() OVER (ORDER BY FNAME,SNAME,ID) rnum,
  6  		 --LEAD(FNAME, 10000, 0) OVER (ORDER BY FNAME,SNAME,ID) inorderjump,
  7  		 FNAME,SNAME,DOB
  8  	     FROM CUSTOMER
  9  	     WHERE FNAME IS NOT NULL AND SNAME IS NOT NULL
 10  	     ORDER BY FNAME,SNAME,ID
 11  	  )
 12  WHERE rnum=10000
 13  /

         I FNAME           SNAME                          DOB
---------- --------------- ------------------------------ ---------
         1 C7-10-15:10:41: /902b0133_DescriptorException  15-OCT-07

Elapsed: 00:00:00.04

Execution Plan
----------------------------------------------------------
Plan hash value: 3488441616

--------------------------------------------------------------------------------------------
| Id  | Operation                     | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |            | 68309 |  6937K| 58561   (1)| 00:11:43 |
|*  1 |  VIEW                         |            | 68309 |  6937K| 58561   (1)| 00:11:43 |
|*  2 |   WINDOW NOSORT STOPKEY       |            | 68309 |  3535K| 58561   (1)| 00:11:43 |
|   3 |    TABLE ACCESS BY INDEX ROWID| CUSTOMER   | 68309 |  3535K| 58561   (1)| 00:11:43 |
|*  4 |     INDEX FULL SCAN           | IDX_USE_ME | 68309 |       |   530   (1)| 00:00:07 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("RNUM"=10000)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY "FNAME","SNAME","ID")<=10000)
   4 - filter("FNAME" IS NOT NULL)

SCOTT@orcl_11g> SELECT i,FNAME,SNAME,DOB
  2  FROM (
  3  	     SELECT /*+ INDEX_ASC(CUSTOMER IDX_USE_ME)*/
  4  		 row_number() OVER (PARTITION BY FNAME,SNAME ORDER BY FNAME,SNAME,ID) i,
  5  		 row_number() OVER (ORDER BY FNAME,SNAME,ID) rnum,
  6  		 --LEAD(FNAME, 10000, 0) OVER (ORDER BY FNAME,SNAME,ID) inorderjump,
  7  		 FNAME,SNAME,DOB
  8  	     FROM CUSTOMER
  9  	     WHERE FNAME IS NOT NULL AND SNAME IS NOT NULL
 10  	     ORDER BY FNAME,SNAME,ID
 11  	  )
 12  WHERE MOD (rnum, 684) <= 3
 13  /

         I FNAME           SNAME                          DOB
---------- --------------- ------------------------------ ---------
         1 @2-08-01:09:48: SDO_LIST_TYPE                  15-OCT-07
         1 @7-04-09:14:17: ST_ANNOTATION_TEXT             15-OCT-07
         1 @7-10-15:10:09: ALL_IND_COLUMNS                15-OCT-07
         1 @7-10-15:10:28: /9330a682_BasicToggleButtonUI  15-OCT-07
         1 @7-10-15:10:28: /94254c62_VMMethodVMMethodImpl 15-OCT-07
         1 @7-10-15:10:28: /9512e7a_AMD64AbstractMIR2LIRC 15-OCT-07
         1 @7-10-15:10:28: /9678561d_ConditionalExpressio 15-OCT-07
         1 @7-10-15:10:30: /a106cfc4_WritableRenderedImag 15-OCT-07
         1 @7-10-15:10:30: /a15b376c_NVListImpl           15-OCT-07
         1 @7-10-15:10:30: /a210f279_CopierManager        15-OCT-07
         1 @7-10-15:10:30: /a2af1500_BasicSplitPaneUIKeyb 15-OCT-07
         1 @7-10-15:10:36: SYS_C003684                    15-OCT-07
         1 @7-10-15:10:36: XDB$ELEMENT_T                  15-OCT-07
         1 @7-10-15:10:36: XDB$INCLUDE_LIST_T             15-OCT-07
         1 @7-10-15:10:36: XDB$XMLTYPE_REF_LIST_T         15-OCT-07
         1 A7-10-15:10:09: V_$SQLAREA                     15-OCT-07
         1 A7-10-15:10:09: V_$SQL_FEATURE_DEPENDENCY      15-OCT-07
         1 A7-10-15:10:09: V_$SQL_PLAN                    15-OCT-07
         1 A7-10-15:10:09: V_$THREAD                      15-OCT-07
         1 A7-10-15:10:28: /ef9b7e35_ConditionalBitPatter 15-OCT-07
         1 A7-10-15:10:28: /f143cb63_BasicProgressBarUICh 15-OCT-07
         1 A7-10-15:10:28: /f15851f5_ExecutePermissionExc 15-OCT-07
         1 A7-10-15:10:28: /f1eac0bd_IA64AbstractMIR2LIRC 15-OCT-07
         1 A7-10-15:10:30: java/awt/geom/QuadIterator     15-OCT-07
         1 A7-10-15:10:30: java/awt/image/BufferStrategy  15-OCT-07
         1 A7-10-15:10:30: java/awt/image/ConvolveOp      15-OCT-07
         1 A7-10-15:10:30: java/beans/BeanDescriptor      15-OCT-07
         1 A7-10-15:10:41: SI_SCOREBYAVGCLR               15-OCT-07
         1 A7-10-15:10:41: SI_SETTEXTUREFTR               15-OCT-07
         1 A7-10-15:10:41: SL_ATTR_T547_T                 15-OCT-07
         1 A7-10-15:10:41: XDj7qm+JEZR2uI0wGKnu+qQg==     15-OCT-07
         1 B7-10-15:10:11: REPCAT_GENERATED               15-OCT-07
         1 B7-10-15:10:11: RULE$                          15-OCT-07
         1 B7-10-15:10:11: SCHEDULER$_ATTRIB_PK           15-OCT-07
         1 B7-10-15:10:11: SCHEDULER$_EVENT_LOG           15-OCT-07
         1 B7-10-15:10:29: /25ef00e8_SecuritySupport125   15-OCT-07
         1 B7-10-15:10:29: /27bbee3d_JDWPStackFrameGetVal 15-OCT-07
         1 B7-10-15:10:29: /28b67e03_Operation            15-OCT-07
         1 B7-10-15:10:29: /28cdf563_ThreadPolicyImpl     15-OCT-07
         1 B7-10-15:10:30: sun/misc/PerformanceLogger     15-OCT-07
         1 B7-10-15:10:30: sun/misc/ProxyGenerator        15-OCT-07
         1 B7-10-15:10:30: sun/nio/ch/DatagramDispatcher  15-OCT-07
         1 B7-10-15:10:30: sun/nio/ch/SocketAdaptor$1     15-OCT-07
         1 B7-10-15:10:48: GV_OLAPI_IFACE_OP_HISTORY      15-OCT-07
         1 B7-10-15:10:48: OLAPIHANDSHAKE2                15-OCT-07
         1 B7-10-15:10:48: oracle/spatial/network/Kruskal 15-OCT-07
         1 B7-10-15:10:49: /5111681e_OraOlapConstants     15-OCT-07
         1 C7-10-15:10:13: USER_CUBE_HIER_LEVELS          15-OCT-07
         1 C7-10-15:10:13: USER_MEASURE_FOLDER_CONTENTS   15-OCT-07
         1 C7-10-15:10:13: USER_QUEUE_SCHEDULES           15-OCT-07
         1 C7-10-15:10:13: USER_SCHEDULER_CHAIN_STEPS     15-OCT-07
         1 C7-10-15:10:29: /718f419e_Constants            15-OCT-07
         1 C7-10-15:10:29: /72285d3a_DatatypeException    15-OCT-07
         1 C7-10-15:10:29: /72b13abc_BasicParserConfigura 15-OCT-07
         1 C7-10-15:10:29: /75db13f6_JDWPEventRequestSetM 15-OCT-07
         1 C7-10-15:10:31: /96ca5e42_Messages_arpropertie 15-OCT-07
         1 C7-10-15:10:31: /98b64cec_OracleErrorsText_zh_ 15-OCT-07
         1 C7-10-15:10:31: /9966bd63_Oracle8JdbcChecker   15-OCT-07
         1 C7-10-15:10:31: /9a3789ab_InvalidRelationIdExc 15-OCT-07
         1 C7-10-15:10:56: WK$INST_ADMIN_LIST             15-OCT-07
         1 C7-10-15:10:56: WK$PT_ID_SEQ                   15-OCT-07
         1 C7-10-15:10:56: WK$SCHED_ID_SEQ                15-OCT-07
         1 C7-10-15:10:56: WK$TRACE_PK                    15-OCT-07
         1 D7-10-15:10:23: OWA_OPT_LOCK                   15-OCT-07
         1 D7-10-15:10:23: SYS_IOT_OVER_11848             15-OCT-07
         1 D7-10-15:10:26: ALL_WORKSPACES                 15-OCT-07
         1 D7-10-15:10:26: OWM_ASSERT_PKG                 15-OCT-07
         1 D7-10-15:10:29: /cba799a5_TransportServiceList 15-OCT-07
         1 D7-10-15:10:29: /cc33bb15_ResourceManager      15-OCT-07
         1 D7-10-15:10:29: /cc620e87_IAND                 15-OCT-07
         1 D7-10-15:10:29: /ccce3e04_RMIConnectorRMIClien 15-OCT-07
         1 D7-10-15:10:31: lib/security/java.security     15-OCT-07
         1 D7-10-15:10:31: oracle/aurora/rdbms/JMXAgent   15-OCT-07
         1 D7-10-15:10:31: oracle/aurora/util/JRIExt      15-OCT-07
         1 D7-10-15:10:31: oracle/aurora/util/TableReader 15-OCT-07
         1 D7-10-15:10:59: MGMT_ECM_SNAPSHOT_PKG          15-OCT-07
         1 D7-10-15:11:00: AQ$_MGMT_PAF_MSG_QTABLE_1_I    15-OCT-07
         1 D7-10-15:11:00: CHECK_DUPLICATE_TARGETS        15-OCT-07
         1 D7-10-15:11:00: EM_LICENSE                     15-OCT-07
         1 E7-10-15:10:28: /24c93e10_IA64AbstractMIR2LIRC 15-OCT-07
         1 E7-10-15:10:28: /255eb681_AnnotationValueImplT 15-OCT-07
         1 E7-10-15:10:28: /25bcb906_ConditionalBitPatter 15-OCT-07
         1 E7-10-15:10:28: /25ce416e_OracleClassLoader    15-OCT-07
         1 E7-10-15:10:29: sun/misc/CacheEntry            15-OCT-07
         1 E7-10-15:10:29: sun/net/www/HeaderParser       15-OCT-07
         1 E7-10-15:10:29: sun/security/jgss/ProviderList 15-OCT-07
         1 E7-10-15:10:29: sun/security/krb5/Checksum     15-OCT-07
         1 E7-10-15:10:33: /1eb71508_XSLJDWPSocketConn    15-OCT-07
         1 E7-10-15:10:33: /2293736_XSLTransformerErrorLi 15-OCT-07
         1 E7-10-15:10:33: /29988fbd_MailcapCommandMap    15-OCT-07
         1 E7-10-15:10:33: /2dfbd72c_ComparisonTerm       15-OCT-07
         1 E7-11-08:05:58: AQ$ORDERS_QUEUETABLE_R         08-NOV-07
         1 E7-11-08:05:58: AQ$_STREAMS_QUEUE_TABLE_H      08-NOV-07
         1 E7-11-08:05:58: COSTS                          08-NOV-07
         1 E7-11-08:05:58: COSTS_PROD_BIX                 08-NOV-07
         1 F7-10-15:10:28: /7df2be4b_BasicComboBoxUI1     15-OCT-07
         1 F7-10-15:10:28: /7ef71be3_VMWellKnownMethodReg 15-OCT-07
         1 F7-10-15:10:28: /7f9d6b06_PowerPCCodeGenerator 15-OCT-07
         1 F7-10-15:10:28: /8136aaa1_ClassReader1         15-OCT-07
         1 F7-10-15:10:30: /846552c0_BaseMetaDataImpl     15-OCT-07
         1 F7-10-15:10:30: /8573778c_WriteContents        15-OCT-07
         1 F7-10-15:10:30: /85db04e1_AttributeNotFoundExc 15-OCT-07
         1 F7-10-15:10:30: /87f55b2d_ImageViewImageLabelV 15-OCT-07
         1 F7-10-15:10:34: oracle/xquery/exec/LogicalOp   15-OCT-07
         1 F7-10-15:10:34: oracle/xquery/exec/PathAxis    15-OCT-07
         1 F7-10-15:10:34: oracle/xquery/func/OraVersion  15-OCT-07
         1 F7-10-15:10:34: oracle/xquery/parser/Qname     15-OCT-07
         1 G7-10-15:10:09: PROXY_ROLE_DATA$               15-OCT-07
         1 G7-10-15:10:09: RESOURCE_MAPPING_PRIORITY$     15-OCT-07
         1 G7-10-15:10:09: RLS_SC$                        15-OCT-07
         1 G7-10-15:10:09: RULESET$                       15-OCT-07
         1 G7-10-15:10:28: /e2cd7a5d_Dominators           15-OCT-07
         1 G7-10-15:10:28: /e2d98d5_IA64BaseLIRInstrFSABu 15-OCT-07
         1 G7-10-15:10:28: /e2ffd5f2_BasicTreeUITreeCance 15-OCT-07
         1 G7-10-15:10:28: /e505feef_AdaptorBootstrapDefa 15-OCT-07
         1 G7-10-15:10:30: /e6ed02b7_VetoableChangeSuppor 15-OCT-07
         1 G7-10-15:10:30: /e7f02dc5__CodeBaseStub        15-OCT-07
         1 G7-10-15:10:30: /e8c9db7b_CDRInputStream_1_0   15-OCT-07
         1 G7-10-15:10:30: /e99d1147_DocumentName         15-OCT-07
         1 G7-10-15:10:41: /a0d62d0d_FileLoadDescriptor   15-OCT-07
         1 G7-10-15:10:41: /a28b5b32_strhChunk            15-OCT-07
         1 G7-10-15:10:41: /a500e316_JaiI18N              15-OCT-07
         1 G7-10-15:10:41: /a8cafc03_MlibTranslateRIF     15-OCT-07
         1 H7-10-15:10:11: ALL_REPGENOBJECTS              15-OCT-07
         1 H7-10-15:10:11: ALL_REPGROUP                   15-OCT-07
         1 H7-10-15:10:11: ALL_RULE_SETS                  15-OCT-07
         1 H7-10-15:10:11: AQ$_SUBSCRIBER_TABLE_PRIMARY   15-OCT-07
         1 H7-10-15:10:28: sqlj/util/Parselet             15-OCT-07
         1 H7-10-15:10:28: sun/swing/FilePane$3           15-OCT-07
         1 H7-10-15:10:28: sun/text/IntHashtable          15-OCT-07
         1 H7-10-15:10:28: sun/tools/asm/CatchData        15-OCT-07
         1 H7-10-15:10:30: javax/swing/text/html/HTML     15-OCT-07
         1 H7-10-15:10:30: javax/swing/tree/RowMapper     15-OCT-07
         1 H7-10-15:10:30: javax/swing/undo/CompoundEdit  15-OCT-07
         1 H7-10-15:10:30: oracle/aurora/vm/IUHandle      15-OCT-07
         1 H7-10-15:10:44: ST_GEOMCOLLECTION              15-OCT-07
         1 H7-10-15:10:45: COORD_REF_SYSTEM_PRIM          15-OCT-07
         1 H7-10-15:10:45: SDO_ADMIN                      15-OCT-07
         1 H7-10-15:10:45: SDO_COORD_OPS                  15-OCT-07
         1 I7-10-15:10:12: ODCIINDEXINFODUMP              15-OCT-07
         1 I7-10-15:10:12: ODCIOBJECTLIST                 15-OCT-07
         1 I7-10-15:10:12: REPCAT$_SITES_NEW_FK2_IDX      15-OCT-07
         1 I7-10-15:10:12: REPCAT$_SITE_OBJECTS           15-OCT-07
         1 I7-10-15:10:29: /5f1659ed_RealType             15-OCT-07
         1 I7-10-15:10:29: /60f1dbe_XMLEntityManagerEntit 15-OCT-07
         1 I7-10-15:10:29: /61027b31_VoidType             15-OCT-07
         1 I7-10-15:10:29: /61734d21_ClassUnloadEvent     15-OCT-07
         1 I7-10-15:10:31: /564607d_HTMLAnchorElement     15-OCT-07
         1 I7-10-15:10:31: /5aaea7ab_OraCustomizerErrorsT 15-OCT-07
         1 I7-10-15:10:31: /5ae161b6_DefaultProxySelector 15-OCT-07
         1 I7-10-15:10:31: /5b3f9016_HtmlDoclet           15-OCT-07
         1 I7-10-15:10:55: ORDMD_TNPC_LIBS                15-OCT-07
         1 I7-10-15:10:55: R_TABLE                        15-OCT-07
         1 I7-10-15:10:55: SDO_WFS_LOCK                   15-OCT-07
         1 I7-10-15:10:55: SDO_WS_CONF_PART_PK            15-OCT-07
         1 J7-10-15:10:17: DBMS_SUMVDM                    15-OCT-07
         1 J7-10-15:10:17: WRI$_ADV_OBJSPACE_TREND_T      15-OCT-07
         1 J7-10-15:10:20: DBMSOBJG                       15-OCT-07
         1 J7-10-15:10:20: DBMS_FEATURE_XDB               15-OCT-07
         1 J7-10-15:10:29: /b93aef44_JDWPStackFrameSetVal 15-OCT-07
         1 J7-10-15:10:29: /b960f153_LdapSchemaCtx        15-OCT-07
         1 J7-10-15:10:29: /ba7fed63_POP                  15-OCT-07
         1 J7-10-15:10:29: /baa7344b_XMLSchemaDescription 15-OCT-07
         1 J7-10-15:10:31: /fab81813_MetalComboBoxUI      15-OCT-07
         1 J7-10-15:10:31: /fc374dcc_ProviderList3        15-OCT-07
         1 J7-10-15:10:31: /fc8d8741_InvalidRoleValueExce 15-OCT-07
         1 J7-10-15:10:31: /fc965149_MultiInternalFrameUI 15-OCT-07
         1 J7-10-15:10:57: PARAM_VALUES_TAB               15-OCT-07
         1 J7-10-15:10:57: PK_MGMT_HTTP_SESS_CBS          15-OCT-07
         1 J7-10-15:10:57: PK_MGMT_UPD_PROP_DATA          15-OCT-07
         1 J7-10-15:10:57: SMP_EMD_TGT_OBJECT             15-OCT-07
         1 K7-10-15:10:27: sun/nio/cs/ext/MS874$Encoder   15-OCT-07
         1 K7-10-15:10:27: sun/nio/cs/ext/MS950$Encoder   15-OCT-07
         1 K7-10-15:10:27: sun/nio/cs/ext/MacRoman        15-OCT-07
         1 K7-10-15:10:27: sun/rmi/rmic/iiop/ArrayType    15-OCT-07
         1 K7-10-15:10:29: /fe43c70e_InvalidName          15-OCT-07
         1 K7-10-15:10:29: /fec01df_SecretKeyFactorySpi   15-OCT-07
         1 K7-10-15:10:29: /fed9699c_ParserTable10        15-OCT-07
         1 K7-10-15:10:29: /fed9c0b5_XSParticleDecl       15-OCT-07
         1 K7-10-15:10:31: sun/io/CharToByteISO8859_9     15-OCT-07
         1 K7-10-15:10:31: sun/io/CharToByteMacCyrillic   15-OCT-07
         1 K7-10-15:10:31: sun/io/CharToByteUTF16         15-OCT-07
         1 K7-10-15:10:31: sun/misc/UCDecoder             15-OCT-07
         1 K7-10-15:11:07: WWV_MIG_ACC_GROUPS             15-OCT-07
         1 K7-10-15:11:07: WWV_MIG_ACC_PAGE_IDX2          15-OCT-07
         1 K7-10-15:11:07: WWV_MIG_ACC_RPT_PERM_IDX1      15-OCT-07
         1 K7-10-15:11:07: WWV_MIG_PROJ_UK1               15-OCT-07
         1 L7-10-15:10:28: /66b8ddfe_BasicSpinnerUI1      15-OCT-07
         1 L7-10-15:10:28: /67033829_AMD64AbstractMIR2LIR 15-OCT-07
         1 L7-10-15:10:28: /698a2ef7_MetalFileChooserUIDi 15-OCT-07
         1 L7-10-15:10:28: /69c0cfae_PolicyTableProxyTabl 15-OCT-07
         1 L7-10-15:10:30: /6cbfe1d2_ServiceDialogCopiesP 15-OCT-07
         1 L7-10-15:10:30: /6ce8cb5e_CertPathBuilder1     15-OCT-07
         1 L7-10-15:10:30: /70bd389c_SwingUtilities21     15-OCT-07
         1 L7-10-15:10:30: /715aeac3_X509CRLSelector      15-OCT-07
         1 L7-10-15:10:34: /f37bb8f4_IDRef                15-OCT-07
         1 L7-10-15:10:34: /f5eb4ddb_XSLTransformerConsta 15-OCT-07
         1 L7-10-15:10:34: /f7fede6b_PathDescendantAxisIt 15-OCT-07
         1 L7-10-15:10:34: /f8f81f1b_Unordered            15-OCT-07
         1 M7-10-15:10:09: GV_$FS_FAILOVER_HISTOGRAM      15-OCT-07
         1 M7-10-15:10:09: GV_$HS_AGENT                   15-OCT-07
         1 M7-10-15:10:09: GV_$METRICGROUP                15-OCT-07
         1 M7-10-15:10:09: GV_$PX_SESSTAT                 15-OCT-07
         1 M7-10-15:10:28: /c632ee28_CEStreamExhausted    15-OCT-07
         1 M7-10-15:10:28: /c63add20_JDBCProfile          15-OCT-07
         1 M7-10-15:10:28: /c6637ff6_ElemJSClassJSMemberI 15-OCT-07
         1 M7-10-15:10:28: /c717867b_PowerPCAbstractMIR2L 15-OCT-07
         1 M7-10-15:10:30: /ca585409_NativeMethodAccessor 15-OCT-07
         1 M7-10-15:10:30: /cc0866a8_IDLTypeHelper        15-OCT-07
         1 M7-10-15:10:30: /cc167f76_CancelRequestMessage 15-OCT-07
         1 M7-10-15:10:30: /cdc4a850_DuplicateName        15-OCT-07
         1 M7-10-15:10:41: /1849964f_Extended             15-OCT-07
         1 M7-10-15:10:41: /1adecf21_MlibWarpGridTableOpI 15-OCT-07
         1 M7-10-15:10:41: /1de0ffbd_comsunmediajaicodeci 15-OCT-07
         1 M7-10-15:10:41: /2294c052_DivideIntoConstOpIma 15-OCT-07
         1 N7-10-15:10:10: EXU8GRS                        15-OCT-07
         1 N7-10-15:10:10: EXU8ICPLSQL                    15-OCT-07
         1 N7-10-15:10:10: EXU8LOBU                       15-OCT-07
         1 N7-10-15:10:10: EXU8PSTU                       15-OCT-07
         1 N7-10-15:10:28: oracle/aurora/util/Statistics  15-OCT-07
         1 N7-10-15:10:28: oracle/i18n/text/OraBoot       15-OCT-07
         1 N7-10-15:10:28: oracle/i18n/text/OraLinguistic 15-OCT-07
         1 N7-10-15:10:28: oracle/jdbc/driver/BufferCache 15-OCT-07
         1 N7-10-15:10:30: javax/imageio/spi/IIORegistry  15-OCT-07
         1 N7-10-15:10:30: javax/management/JMException   15-OCT-07
         1 N7-10-15:10:30: javax/net/SocketFactory        15-OCT-07
         1 N7-10-15:10:30: javax/print/AttributeException 15-OCT-07
         1 N7-10-15:10:42: /96aa6169_DICOMImage           15-OCT-07
         1 N7-10-15:10:42: /97b6ceb_MlibMinFilterOpImage  15-OCT-07
         1 N7-10-15:10:42: /9c76c79f_PICTImageEncoder     15-OCT-07
         1 N7-10-15:10:42: /a042a1b6_IIPResolutionOpImage 15-OCT-07
         1 O7-10-15:10:12: DBMS_ADDM                      15-OCT-07
         1 O7-10-15:10:12: DBMS_APPLY_POSITION            15-OCT-07
         1 O7-10-15:10:12: DBMS_AQ_EXP_QUEUES             15-OCT-07
         1 O7-10-15:10:12: DBMS_ASSERT                    15-OCT-07
         1 O7-10-15:10:29: /3babcd25_MessageToken_v2Messa 15-OCT-07
         1 O7-10-15:10:29: /3be9e866_ThreadDeathRequest   15-OCT-07
         1 O7-10-15:10:29: /3c8aecb7_ForwardRequestHelper 15-OCT-07
         1 O7-10-15:10:29: /3cbd32f6_AiffFileFormat       15-OCT-07
         1 O7-10-15:10:31: /46ee7ad6_AnySeqHelper         15-OCT-07
         1 O7-10-15:10:31: /476fa8b6_DefaultLoaderReposit 15-OCT-07
         1 O7-10-15:10:31: /48429d3_SerProfileToClassErro 15-OCT-07
         1 O7-10-15:10:31: /4a07f57a_Messages_japropertie 15-OCT-07
         1 O7-10-15:10:49: CWM2_OLAP_TABLE_MAP            15-OCT-07
         1 O7-10-15:10:49: CWM_OLAP_LEVEL                 15-OCT-07
         1 O7-10-15:10:49: DBA$OLAP2ULEVEL_KEY_COL_USES   15-OCT-07
         1 O7-10-15:10:49: DBA$OLAP2_AGGREGATION_USES     15-OCT-07
         1 P7-10-15:10:15: USER_ADVISOR_SQLW_TABVOL       15-OCT-07
         1 P7-10-15:10:15: USER_FLASHBACK_ARCHIVE_TABLES  15-OCT-07
         1 P7-10-15:10:15: USER_SQLSET                    15-OCT-07
         1 P7-10-15:10:15: V$LOGSTDBY                     15-OCT-07
         1 P7-10-15:10:29: /8f744aff_ProxyInputStream     15-OCT-07
         1 P7-10-15:10:29: /903ee3d1_IDLID                15-OCT-07
         1 P7-10-15:10:29: /9053dfe4_ClientRequestInfo    15-OCT-07
         1 P7-10-15:10:29: /912c89ad_DynSequenceImpl      15-OCT-07
         1 P7-10-15:10:31: /e13e8896_Messages_iw          15-OCT-07
         1 P7-10-15:10:31: /e1cee4a_TrustManagerFactory   15-OCT-07
         1 P7-10-15:10:31: /e3d5b152_DSAKeyPairGenerator  15-OCT-07
         1 P7-10-15:10:31: /e407dde1_ToolEnv              15-OCT-07
         1 P7-10-15:10:57: MGMT_JOB_EXEC_SUMM_IDX02       15-OCT-07
         1 P7-10-15:10:57: MGMT_JOB_LOCK_INFO             15-OCT-07
         1 P7-10-15:10:57: MGMT_JOB_SHORT_STR_ARR_TABLE   15-OCT-07
         1 P7-10-15:10:57: MGMT_JOB_TARGET_LIST           15-OCT-07
         1 Q7-10-15:10:27: sun/io/CharToByteSJIS          15-OCT-07
         1 Q7-10-15:10:27: sun/misc/FIFOQueueEnumerator   15-OCT-07
         1 Q7-10-15:10:27: sun/misc/Perf$1                15-OCT-07
         1 Q7-10-15:10:27: sun/misc/TimerThread           15-OCT-07
         1 Q7-10-15:10:29: /e8ac092_ClassLoaderReference  15-OCT-07
         1 Q7-10-15:10:29: /ea01ee55_ResolveResolveError  15-OCT-07
         1 Q7-10-15:10:29: /ea146b02_HierMemDirCtxHierCon 15-OCT-07
         1 Q7-10-15:10:29: /eab14762_VersionHelper123     15-OCT-07
         1 Q7-10-15:10:31: sqlj/mesg/OptionDescText_nl    15-OCT-07
         1 Q7-10-15:10:31: sqlj/mesg/SyntaxErrorsText_iw  15-OCT-07
         1 Q7-10-15:10:31: sqlj/mesg/SyntaxErrorsText_tr  15-OCT-07
         1 Q7-10-15:10:31: sqlj/tools/Timing              15-OCT-07
         1 Q7-10-15:11:07: WWV_BIU_STEP_ITEM_HELP         15-OCT-07
         1 Q7-10-15:11:07: WWV_FLOW_APP_BUILD_PREF        15-OCT-07
         1 Q7-10-15:11:07: WWV_FLOW_CHARSETS              15-OCT-07
         1 Q7-10-15:11:07: WWV_FLOW_COMPANIES_IDX1        15-OCT-07
         1 R7-10-15:10:28: /4c435694_T2SResultSetAccessor 15-OCT-07
         1 R7-10-15:10:28: /4ce5062_RejectedExecutionExce 15-OCT-07
         1 R7-10-15:10:28: /4cf8101d_PermissionImpl       15-OCT-07
         1 R7-10-15:10:28: /4dab6ffd_PositiveExpression   15-OCT-07
         1 R7-10-15:10:30: /3b8ba89b_GLXRemoteOffScreenIm 15-OCT-07
         1 R7-10-15:10:30: /3beabd3_MBeanServerDelegateIm 15-OCT-07
         1 R7-10-15:10:30: /3c96b43e_SunDragSourceContext 15-OCT-07
         1 R7-10-15:10:30: /3cfc2731_ComponentAccessibleA 15-OCT-07
         1 R7-10-15:10:34: /1f65c6ab_XSLJDWPEventValue4Co 15-OCT-07
         1 R7-10-15:10:34: /20f12cf_OracleXMLSQLException 15-OCT-07
         1 R7-10-15:10:34: /39f3398a_InfosetWriter        15-OCT-07
         1 R7-10-15:10:34: /3bc6dd8d_ExprSequenceExprSequ 15-OCT-07
         1 S7-10-15:10:09: COL_USAGE$                     15-OCT-07
         1 S7-10-15:10:09: DBA_LOG_GROUP_COLUMNS          15-OCT-07
         1 S7-10-15:10:09: DBA_SEQUENCES                  15-OCT-07
         1 S7-10-15:10:09: DBA_TABLES                     15-OCT-07
         1 S7-10-15:10:28: /ad0cf592_BasicInternalFrameUI 15-OCT-07
         1 S7-10-15:10:28: /ad614359_MetalFileChooserUIFi 15-OCT-07
         1 S7-10-15:10:28: /adf105a6_OracleFailoverWorker 15-OCT-07
         1 S7-10-15:10:28: /ae994ed1_SimplerBitPattern    15-OCT-07
         1 S7-10-15:10:30: /a062817_HashDocAttributeSet   15-OCT-07
         1 S7-10-15:10:30: /a327d933_BasicGraphicsUtils   15-OCT-07
         1 S7-10-15:10:30: /a347b0ca_JTextComponentAccess 15-OCT-07
         1 S7-10-15:10:30: /a529818c_VMManagementImpl     15-OCT-07
         1 S7-10-15:10:37: SYS_C003709                    15-OCT-07
         1 S7-10-15:10:37: UNDER_PATH                     15-OCT-07
         1 S7-10-15:10:37: XDB$TTSET                      15-OCT-07
         1 S7-10-15:10:37: XMLBINARYOUTPUTSTREAM          15-OCT-07
         1 T7-10-15:10:10: ALL_EDITIONING_VIEWS_AE        15-OCT-07
         1 T7-10-15:10:10: ALL_ERRORS                     15-OCT-07
         1 T7-10-15:10:10: ALL_INDEXTYPES                 15-OCT-07
         1 T7-10-15:10:10: ALL_LIBRARIES                  15-OCT-07
         1 T7-10-15:10:28: /ff170cf8_BasicDesktopPaneUI1  15-OCT-07
         1 T7-10-15:10:28: /ff337f46_OraLocaleInfoCachedC 15-OCT-07
         1 T7-10-15:10:28: com/sun/jmx/snmp/SnmpIpAddress 15-OCT-07
         1 T7-10-15:10:28: com/sun/jmx/snmp/SnmpMessage   15-OCT-07
         1 T7-10-15:10:30: java/io/ObjectStreamField      15-OCT-07
         1 T7-10-15:10:30: java/lang/Character            15-OCT-07
         1 T7-10-15:10:30: java/lang/Class$2              15-OCT-07
         1 T7-10-15:10:30: java/lang/InterruptedException 15-OCT-07
         1 T7-10-15:10:42: /27772a5f_GIFImageDecoder      15-OCT-07
         1 T7-10-15:10:42: /285d736f_RegistryFileParser   15-OCT-07
         1 T7-10-15:10:42: /2db36ed3_ScaleGeneralOpImage  15-OCT-07
         1 T7-10-15:10:42: /2fdec9e2_ConjugateDescriptor  15-OCT-07
         1 U7-10-15:10:11: USER_REFRESH_CHILDREN          15-OCT-07
         1 U7-10-15:10:11: USER_REPCONFLICT               15-OCT-07
         1 U7-10-15:10:11: USER_REPPRIORITY               15-OCT-07
         1 U7-10-15:10:11: USER_REPPROP                   15-OCT-07
         1 U7-10-15:10:29: /26bc7544_MultiDOMAxisIterator 15-OCT-07
         1 U7-10-15:10:29: /27dd1f96_XSLMessages          15-OCT-07
         1 U7-10-15:10:29: /2843aa40_SecuritySupport125   15-OCT-07
         1 U7-10-15:10:29: /2910abf2_TreeReturn           15-OCT-07
         1 U7-10-15:10:31: /110b84a_CharConvRepackage     15-OCT-07
         1 U7-10-15:10:31: /131de077_ShellServer          15-OCT-07
         1 U7-10-15:10:31: /13862faa_TranslatorErrorsText 15-OCT-07
         1 U7-10-15:10:31: /190cf850_NTLoginModule        15-OCT-07
         1 U7-10-15:10:49: /85fdcbbc_ErrorClassEnum       15-OCT-07
         1 U7-10-15:10:49: /9c15feb7_AttributeProjection  15-OCT-07
         1 U7-10-15:10:49: /b4e6393_ExceptionBundle_ca    15-OCT-07
         1 U7-10-15:10:49: /c6852306_ExternalSourceExpres 15-OCT-07
         1 V7-10-15:10:14: SYS_YOID0000009188$            15-OCT-07
         1 V7-10-15:10:14: SYS_YOID0000009240$            15-OCT-07
         1 V7-10-15:10:15: ALL_CAPTURE                    15-OCT-07
         1 V7-10-15:10:15: DATAPUMP_DDL_TRANSFORM_PARAMS  15-OCT-07
         1 V7-10-15:10:29: /7e8f9ad2_VersionHelper125     15-OCT-07
         1 V7-10-15:10:29: /7e90ecfe_ValidationState      15-OCT-07
         1 V7-10-15:10:29: /7fdd8144_TABLESWITCH          15-OCT-07
         1 V7-10-15:10:29: /802be2f4_XSImplementation     15-OCT-07
         1 V7-10-15:10:31: /9f659358_ActivationActivation 15-OCT-07
         1 V7-10-15:10:31: /a1a6a544_NumberUpSupported    15-OCT-07
         1 V7-10-15:10:31: /a248592e_OpaqueCopyAnyToArgb  15-OCT-07
         1 V7-10-15:10:31: /a405256b_MultiTextUI          15-OCT-07
         1 V7-10-15:10:57: MGMT_AGGR_MEMBERS              15-OCT-07
         1 V7-10-15:10:57: MGMT_ARU_FAM_PRD_PK            15-OCT-07
         1 V7-10-15:10:57: MGMT_BCN_ARRAY                 15-OCT-07
         1 V7-10-15:10:57: MGMT_BCN_STEP_DEFN             15-OCT-07
         1 W7-10-15:10:26: WM$UDTRIG_INFO                 15-OCT-07
         1 W7-10-15:10:26: WM_CONTAINS                    15-OCT-07
         1 W7-10-15:10:27: /1352fb09_ConfirmRemovePolicyE 15-OCT-07
         1 W7-10-15:10:27: /180c0e64_OCSPRequest1         15-OCT-07
         1 W7-10-15:10:29: /dc077e2_Constant              15-OCT-07
         1 W7-10-15:10:29: /dcbe5a83_ExceptionMapper      15-OCT-07
         1 W7-10-15:10:29: /dd0ef30e_Stub                 15-OCT-07
         1 W7-10-15:10:29: /dd512c97_ORBImpl5             15-OCT-07
         1 W7-10-15:10:31: oracle/i18n/data/lx200ab.glb   15-OCT-07
         1 W7-10-15:10:31: oracle/i18n/data/lx200c5.glb   15-OCT-07
         1 W7-10-15:10:31: oracle/i18n/data/lx200f1.glb   15-OCT-07
         1 W7-10-15:10:31: oracle/i18n/data/lx20147.glb   15-OCT-07
         1 W7-10-15:11:00: MGMT_OSM_DISK_GROUP_ECM_PK     15-OCT-07
         1 W7-10-15:11:00: MGMT_PAF_PROCEDURES_UNIQ       15-OCT-07
         1 W7-10-15:11:00: MGMT_RT_METRICS_RAW_TR         15-OCT-07
         1 W7-10-15:11:00: MGMT_TARGETS                   15-OCT-07
         1 X7-10-15:10:28: /38ae8be9_JDMAclItem           15-OCT-07
         1 X7-10-15:10:28: /39366e3e_SqljInterfaceTransfo 15-OCT-07
         1 X7-10-15:10:28: /39d6de9e_SmartGridLayout      15-OCT-07
         1 X7-10-15:10:28: /3a26ce89_DigCode              15-OCT-07
         1 X7-10-15:10:30: /1bc18f90_ContainerAccessibleA 15-OCT-07
         1 X7-10-15:10:30: /1d493451_X509EncodedKeySpec   15-OCT-07
         1 X7-10-15:10:30: /1e1073ec_ObjectAlreadyActive  15-OCT-07
         1 X7-10-15:10:30: /1f4a4c54_JSpinnerDefaultEdito 15-OCT-07
         1 X7-10-15:10:33: /74fe3eaa_XMLPrintDriver       15-OCT-07
         1 X7-10-15:10:33: /7aa29152_XSLOutputCharacter   15-OCT-07
         1 X7-10-15:10:33: /861dc517_MessageChangedListen 15-OCT-07
         1 X7-10-15:10:33: /8da70af4_StaticBaseURI        15-OCT-07
         1 X7-11-08:06:03: XDB_DOM_HELPER                 08-NOV-07
         1 X7-11-08:23:02: FORMAT_STRING                  08-NOV-07
         1 X7-12-18:08:57: BUS_STATE_HIST                 18-DEC-07
         1 X7-12-20:11:19: EMP_DETAILS_OBJ                20-DEC-07
         1 Y7-10-15:10:28: /7b391ae2_DBConversionUnicodeS 15-OCT-07
         1 Y7-10-15:10:28: /7c271cf6_UTF8ValidationFilter 15-OCT-07
         1 Y7-10-15:10:28: /7e5a944a_RawFmt               15-OCT-07
         1 Y7-10-15:10:28: /7eec0634_JvmThreadingMetaImpl 15-OCT-07
         1 Y7-10-15:10:30: /8e22730_ProxyGeneratorProxyMe 15-OCT-07
         1 Y7-10-15:10:30: /8f039248_JSeparatorAccessible 15-OCT-07
         1 Y7-10-15:10:30: /8f0f5f28_ServiceDialogAppeara 15-OCT-07
         1 Y7-10-15:10:30: /8fd57f4d_ComponentViewInvalid 15-OCT-07
         1 Y7-10-15:10:35: DRISGP                         15-OCT-07
         1 Y7-10-15:10:35: DRX$IXV_KEY                    15-OCT-07
         1 Y7-10-15:10:35: EXF$ATTRLIST                   15-OCT-07
         1 Y7-10-15:10:35: EXF$CHECK_PRIVILEGE            15-OCT-07

399 rows selected.

Elapsed: 00:00:00.97

Execution Plan
----------------------------------------------------------
Plan hash value: 2484335325

--------------------------------------------------------------------------------------------
| Id  | Operation                     | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |            | 68309 |  6937K| 58561   (1)| 00:11:43 |
|*  1 |  VIEW                         |            | 68309 |  6937K| 58561   (1)| 00:11:43 |
|   2 |   WINDOW NOSORT               |            | 68309 |  3535K| 58561   (1)| 00:11:43 |
|   3 |    TABLE ACCESS BY INDEX ROWID| CUSTOMER   | 68309 |  3535K| 58561   (1)| 00:11:43 |
|*  4 |     INDEX FULL SCAN           | IDX_USE_ME | 68309 |       |   530   (1)| 00:00:07 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(MOD("RNUM",684)<=3)
   4 - filter("FNAME" IS NOT NULL)

SCOTT@orcl_11g> 


[edit: I removed the portion of my post that belonged in another thread]

[Updated on: Sun, 30 March 2008 14:42]

Report message to a moderator

Re: Bucketing Query Performance [message #309940 is a reply to message #309928] Sun, 30 March 2008 04:54 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Hey Barbara, you're mixing the threads and making my brain hurt.

But it's good to know the syntax was either right, or close enough so you could make it work. The Explain Plan doesn't tell the full story though because the CURSOR() is not shown. You would need to TKPROF it.

For this thread, I'd be interested to know how @coleing's suggestion of tacking DOB onto the index compares performance-wise.

Ross Leishman
Re: Bucketing Query Performance [message #309952 is a reply to message #309865] Sun, 30 March 2008 09:46 Go to previous messageGo to next message
verpies
Messages: 28
Registered: March 2008
Location: Seattle
Junior Member
Coleing,

Adding the DOB to the index indeed speeds up the query 2x.

However, I cannot go that route, because I am restricted to indices that already exist. The DBA will only give me permissions to create an index for the TEST orauser.

This is something I neglected to mention in my original problem specification.


Thanks,
George

"coleing wrote on Sat, 29 March 2008 23:43"
You could add DOB to the index to eliminate the table access. That is all I can think of.

[Updated on: Sun, 30 March 2008 10:19]

Report message to a moderator

Re: Bucketing Query Performance [message #309957 is a reply to message #309928] Sun, 30 March 2008 10:38 Go to previous messageGo to next message
verpies
Messages: 28
Registered: March 2008
Location: Seattle
Junior Member
Hey Barbara,

Now you are getting the same plan as I am for the query:
SELECT i,FNAME,SNAME,DOB
FROM (
       SELECT /*+ INDEX_ASC(CUSTOMER IDX_USE_ME)*/
          row_number() OVER (PARTITION BY FNAME,SNAME ORDER BY FNAME,SNAME,ID) i,
          row_number() OVER (ORDER BY FNAME,SNAME,ID) rnum,
          --LEAD(FNAME, 10000, 0) OVER (ORDER BY FNAME,SNAME,ID) inorderjump,
 	  FNAME,SNAME,DOB
       FROM CUSTOMER
       WHERE FNAME IS NOT NULL AND SNAME IS NOT NULL
       ORDER BY FNAME,SNAME,ID
      )
WHERE rnum = :X

Please note that the query execution time increases linearly with :X
This is to be expected because each time the query is restarted , the query begins to scan all the records from the beginning and has further and further to scan as :X increases.

So if we did a ridiculous query like this:
SELECT i,FNAME,SNAME,DOB
FROM (
       SELECT /*+ INDEX_ASC(CUSTOMER IDX_USE_ME)*/
          row_number() OVER (PARTITION BY FNAME,SNAME ORDER BY FNAME,SNAME,ID) i,
          row_number() OVER (ORDER BY FNAME,SNAME,ID) rnum,
          --LEAD(FNAME, 10000, 0) OVER (ORDER BY FNAME,SNAME,ID) inorderjump,
 	  FNAME,SNAME,DOB
       FROM CUSTOMER
       WHERE FNAME IS NOT NULL AND SNAME IS NOT NULL
       ORDER BY FNAME,SNAME,ID
      )
WHERE rnum = 1
UNION ALL
SELECT i,FNAME,SNAME,DOB
FROM (
       SELECT /*+ INDEX_ASC(CUSTOMER IDX_USE_ME)*/
          row_number() OVER (PARTITION BY FNAME,SNAME ORDER BY FNAME,SNAME,ID) i,
          row_number() OVER (ORDER BY FNAME,SNAME,ID) rnum,
          --LEAD(FNAME, 10000, 0) OVER (ORDER BY FNAME,SNAME,ID) inorderjump,
 	  FNAME,SNAME,DOB
       FROM CUSTOMER
       WHERE FNAME IS NOT NULL AND SNAME IS NOT NULL
       ORDER BY FNAME,SNAME,ID
      )
WHERE rnum = 10000
UNION ALL
SELECT i,FNAME,SNAME,DOB
FROM (
       SELECT /*+ INDEX_ASC(CUSTOMER IDX_USE_ME)*/
          row_number() OVER (PARTITION BY FNAME,SNAME ORDER BY FNAME,SNAME,ID) i,
          row_number() OVER (ORDER BY FNAME,SNAME,ID) rnum,
          --LEAD(FNAME, 10000, 0) OVER (ORDER BY FNAME,SNAME,ID) inorderjump,
 	  FNAME,SNAME,DOB
       FROM CUSTOMER
       WHERE FNAME IS NOT NULL AND SNAME IS NOT NULL
       ORDER BY FNAME,SNAME,ID
      )
WHERE rnum = 20000
-- UNION ALL ...97 more times
...then the performance would be awful because it would follow the arithmetical progression 1+2+3+4+5+6...etc...

The following query eliminates 99 of these UNION ALLs, but suffers from the same arithmetical progression problem:
SELECT i,FNAME,SNAME,DOB
FROM
      ( 
        SELECT /*+ INDEX_ASC(CUSTOMER IDX_USE_ME)*/
            row_number() OVER (PARTITION BY FNAME,SNAME ORDER BY FNAME,SNAME,ID) i,
            row_number() OVER (ORDER BY FNAME,SNAME,ID) rnum,
            FNAME, SNAME, DOB
        FROM CUSTOMER
        WHERE FNAME IS NOT NULL AND SNAME IS NOT NULL
        ORDER BY FNAME,SNAME,ID
      ) a
JOIN      
      (
        SELECT 1+rownum*10000 AS rnum
  	FROM   DUAL
  	CONNECT BY LEVEL <= 99
      ) b
ON a.rnum=b.rnum


BUT what if we did not start from the beginning each time, but from the row where the previous query finished ?
This would mean adding a condition on FNAME, SMAME, ID to the inner WHERE clause or maybe CONNECT BY PRIOR...
Thus, in the subsequent query, an index could be used to find that new starting row almost instantly, and WINDOW NOSORT STOPKEY would jump forward from that row.

Do you think it's doable ?


Regards,
George

[Updated on: Sun, 30 March 2008 14:20]

Report message to a moderator

Re: Bucketing Query Performance [message #309967 is a reply to message #309940] Sun, 30 March 2008 14:44 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8623
Registered: November 2002
Location: California, USA
Senior Member
rleishman wrote on Sun, 30 March 2008 02:54
Hey Barbara, you're mixing the threads and making my brain hurt.



Sorry about that. It wasn't intentional. I removed the portion of my prior post that belonged in the original poster's other thread. I remember that I was tired and had a headache and apparently I gave you one.

Re: Bucketing Query Performance [message #309976 is a reply to message #309967] Sun, 30 March 2008 18:13 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
It's the "jumping forward" that will get you. If you want to jump forward a predetermined number of rows, you have to count those rows, which means reading them. Damage done!

As for not being able to create an index: Congratulations, your work is done. A DBA that refuses to create an index that demontrably imrpoves performance of a critical (is this critical?) query ASSUMES RESPONSIBILITY FOR THE PROBLEM THEMSELVES.

Ross Leishman
Re: Bucketing Query Performance [message #310575 is a reply to message #309976] Tue, 01 April 2008 13:09 Go to previous messageGo to next message
verpies
Messages: 28
Registered: March 2008
Location: Seattle
Junior Member
Quote:
It's the "jumping forward" that will get you


Yes, I was just hoping that it would be faster than doing the 2sec JOIN which starts from the beginning each time.

The most frustrating thing for me as a low-level programmer is that I can see that Oracle has the means of doing such a jump more efficiently than going through all the rows in between.

When one looks at the index dump available at:
http://www.orafaq.com/node/1403]

----- begin tree dump
branch: 0x68066c8 109078216 (0: nrow: 325, level: 1)
   leaf: 0x68066c9 109078217 (-1: nrow: 694 rrow: 694)
   leaf: 0x68066ca 109078218 (0: nrow: 693 rrow: 693)
   leaf: 0x68066cb 109078219 (1: nrow: 693 rrow: 693)
   leaf: 0x68066cc 109078220 (2: nrow: 693 rrow: 693)
   leaf: 0x68066cd 109078221 (3: nrow: 693 rrow: 693)
   ...
   ...
   leaf: 0x68069cf 109078991 (320: nrow: 763 rrow: 763)
   leaf: 0x68069d0 109078992 (321: nrow: 761 rrow: 761)
   leaf: 0x68069d1 109078993 (322: nrow: 798 rrow: 798)
   leaf: 0x68069d2 109078994 (323: nrow: 807 rrow: 807)
----- end tree dump

.. it can be seen that by knowing the total number of rows and performing a running sum of the nrow values, whole branch/leaf blocks could be skipped without going through their contents. That would be reasonably quick way to "jump ahead".

I am surprised that Oracle developers are not using this block skipping technique in some of the built-in functions, e.g.: LEAD, LAG.

Regards,
George

P.S.
I am far from defending my overzealous DBA, but the sorting order (e.g.: FNAME, SNAME, ID) depends on the users of the system, and if he let a new index be created instead of an existing one being reused, soon the users would create many redundant indices. So I understand his reasons.

[Updated on: Tue, 01 April 2008 13:10]

Report message to a moderator

Re: Bucketing Query Performance [message #310587 is a reply to message #309777] Tue, 01 April 2008 13:57 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
Yes indeed. Index skipping would be great (Even though Oracle can do index skipping for non-leading index keys - see index_skip_scan), so in theory you are right - I think.

However, just a final attempt at a solution for you.

can you create a fast refreshing materialized view, with the first, second and third max value for each FNAME, SNAME, ID.

Then reading from that will just be a constant 250 row table!

Although it would mean you need an mview log on CUSTOMER to maintain the updates.

Just a thought though.

Incidentally, adding DOB to the existing index, rather than creating a new one shouldnt cause any harm to anyone else, its just an extra column.

Re: Bucketing Query Performance [message #310629 is a reply to message #310575] Tue, 01 April 2008 17:34 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
verpies wrote on Wed, 02 April 2008 04:09
When one looks at the index dump available at:
http://www.orafaq.com/node/1403]

----- begin tree dump
branch: 0x68066c8 109078216 (0: nrow: 325, level: 1)
   leaf: 0x68066c9 109078217 (-1: nrow: 694 rrow: 694)
   leaf: 0x68066ca 109078218 (0: nrow: 693 rrow: 693)
   leaf: 0x68066cb 109078219 (1: nrow: 693 rrow: 693)
   leaf: 0x68066cc 109078220 (2: nrow: 693 rrow: 693)
   leaf: 0x68066cd 109078221 (3: nrow: 693 rrow: 693)
   ...
   ...
   leaf: 0x68069cf 109078991 (320: nrow: 763 rrow: 763)
   leaf: 0x68069d0 109078992 (321: nrow: 761 rrow: 761)
   leaf: 0x68069d1 109078993 (322: nrow: 798 rrow: 798)
   leaf: 0x68069d2 109078994 (323: nrow: 807 rrow: 807)
----- end tree dump

.. it can be seen that by knowing the total number of rows and performing a running sum of the nrow values, whole branch/leaf blocks could be skipped without going through their contents. That would be reasonably quick way to "jump ahead".


This is exactly what I was talking about before. These row counts in the index are not stored in some easily and cheaply accessible location; they are stored in the index leaf blocks themselves.

If you want to jump forward (say) 1000 rows) then you must read the leaf block from disk, load it into the buffer cache, read the header, then discard it if the row count is less than the jump-forward number.

The expensive part of this operation is reading from disk into the buffer cache. Once you have committed to do that, any hope of serious tuning is lost.

Where you ARE losing out is that it appears Oracle is reading the table to get the DOB even for discarded rows. As discussed, this is avoidable.

I hear what you're saying about the DBA, but by the look of your posts here and on AskTom, you have spent at least $20,000 of your client/employer's money trying to make this thing perform to a required benchmark. Now unless you have gone rogue and are acting without the support and knowledge of your employer, I call that a critical business requirement.

These are the things that DBAs are paid to fix. The problem (with apologies to the DBA's out there) is that most DBA's are -in part - PROCESS workers, whereas programmers are PROJECT workers. It is process workers job to maintain the status-quo and protect their company from the negative effects of change. It is the project-workers job to drag the company kicking-and-screaming into change for the greater good despite the inevitable negative effects.

DBAs have a dual-role: they have to maintain the status-quo and keep the database performance and availability high. But they also have to support developers, which means supporting change. This makes them conflicted, and process often wins out over project.
[/rant]

Anyway, you can mitigate the DOB lookup for discarded rows with something that I usually recommend against (see http://www.orafaq.com/node/1981).

SELECT i,FNAME,SNAME,(select DOB from customer where rowid = row_id) AS DOB
FROM (
        SELECT /*+ INDEX_ASC(CUSTOMER IDX_USE_ME)*/
          row_number() OVER (PARTITION BY FNAME,SNAME ORDER BY FNAME,SNAME,ID) i,
          FNAME,
          SNAME,
          ROWID AS row_id,
          rownum rnum
        FROM CUSTOMER
        WHERE FNAME IS NOT NULL AND SNAME IS NOT NULL         
        ORDER BY FNAME,SNAME,ID
     )
WHERE mod(rnum,:RPB)<3;


Ross Leishman
Previous Topic: Oracle execution plan help needed
Next Topic: optimzier RULE
Goto Forum:
  


Current Time: Sat Dec 03 08:22:59 CST 2016

Total time taken to generate the page: 0.08045 seconds