Solved

INDEX_DESC - Oracle

Posted on 2007-04-09
3
2,106 Views
Last Modified: 2012-06-27
I am trying to run the following query:

select /*+ INDEX_DESC(RPM_PRODUCTIONHEADER, PRODUCTION_ENDTIME_IDX) */  productionno, production_endtime
from rpm_productionheader

Here is my table:

CREATE TABLE TOP.RPM_PRODUCTIONHEADER
(
    PRODUCTIONNO                   VARCHAR2(128) NOT NULL,
    PRODUCTRE_ID                   VARCHAR2(128),
    PRODUCTREVERSION               VARCHAR2(16),
    PRODUCTID                      VARCHAR2(128),
    PRODUCTGRADE                   VARCHAR2(16),
    PRODUCTIONQUANTITY             NUMBER(9,3),
    PRODUCTIONUNIT                 VARCHAR2(32),
    PRODUCTIONPERIODTIME           DATE,
    PRODUCTIONSCHEDSTARTTIME       DATE,
    PRODUCTIONSCHEDENDTIME         DATE,
    ORDERID                        VARCHAR2(128) NOT NULL,
    ORDERBRANCHID                  VARCHAR2(128),
    PRODUCTIONLOTID                VARCHAR2(128),
    PRODUCTIONSTATUS               NUMBER(2,0) NOT NULL,
    PRODUCTIONRESULTQUANTITY       NUMBER(9,3),
    DESCRIPTION                    VARCHAR2(255),
    EQUIPPATHID                    VARCHAR2(32) NOT NULL,
    PRODUCTION_INPUT_QUANTITY      NUMBER(9,3),
    PROCESS_KIND                   VARCHAR2(1) NOT NULL,
    PROCESSING_ORDER               NUMBER(2,0) NOT NULL,
    PRODUCTION_STARTTIME           DATE,
    PRODUCTION_ENDTIME             DATE,
    ERP_PRODUCTIONNO               VARCHAR2(128) NOT NULL,
    PRODUCTION_LOADER_QUANTITY     NUMBER(9,3),
    CONSTRAINT RPM_PRODUCTIONHEADER_IDX PRIMARY KEY (PRODUCTIONNO) USING INDEX
        PCTFREE 10
        INITRANS 2
        MAXTRANS 255
        TABLESPACE EFACT_TBL
        STORAGE(INITIAL 13384K MINEXTENTS 1 MAXEXTENTS 2147483645 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
        LOGGING
)
PCTFREE 10
PCTUSED 40
MAXTRANS 255
TABLESPACE EFACT_TBL
STORAGE(INITIAL 104064K MINEXTENTS 1 MAXEXTENTS 2147483645 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
NOCACHE
LOGGING
/
COMMENT ON COLUMN TOP.RPM_PRODUCTIONHEADER.PRODUCTIONSTATUS IS '10:RECIEVED 30:WORKING 60:FINISHED 70:SENT'
/

Here is the index:

CREATE INDEX TOP.PRODUCTION_ENDTIME_IDX
ON TOP.RPM_PRODUCTIONHEADER
(PRODUCTION_ENDTIME)
PCTFREE 10
INITRANS 2
MAXTRANS 255
TABLESPACE EFACT_TBL
STORAGE(INITIAL 64K MINEXTENTS 1 MAXEXTENTS 2147483645 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
LOGGING
/

for some reason the query is not working.  There are no error messages, but the data is not sorted (by date desc).  Here is my oracle information:

BANNER
----------------------------------------------------------------
Oracle8i Release 8.1.7.4.1 - Production
PL/SQL Release 8.1.7.3.0 - Production
CORE      8.1.7.2.1      Production
TNS for 32-bit Windows: Version 8.1.7.4.0 - Production
NLSRTL Version 3.4.1.0.0 - Production

Any ideas?
0
Comment
Question by:NeoTek
  • 2
3 Comments
 
LVL 23

Accepted Solution

by:
paquicuba earned 300 total points
ID: 18880122
INDEX_DESC only scans the index range in descending order, but it doesn't sort the result set. You need to use the ORDER BY clause in order to sort it.
Create index columns in descending order to improve the performance of queries that order results in descending order or that search for the minimum or maximum value of an indexed column.

In you case:
CREATE INDEX TOP.PRODUCTION_ENDTIME_IDX
ON TOP.RPM_PRODUCTIONHEADER
(PRODUCTION_ENDTIME DESC)
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 18880128
I meant Ïn your case
0
 
LVL 1

Author Comment

by:NeoTek
ID: 18882963
For some reason, your SQL statement creates the index, but the newly created index doesn't have any columns.

Anyway, what I really want to do is recreate the TOP N functionality of MS SQL.  I don't have the enterprise edition
or Orcale so I can't use Rank.  I also don't want to nest SQL statements (like the one below) because it seems to take
too long.  I think using the index (if possible) would be much faster.

SQL Statement:

SELECT PRODUCTIONNO, PRODUCTION_ENDTIME
FROM (
SELECT *
FROM RPM_ProductionHeader
ORDER BY PRODUCTION_ENDTIME
) WHERE ROWNUM <= 10

Is what I am describing possible, or should I just use the SQL statement above?
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
oracle query help 29 64
Move SQL 2005 Express to Server 2012R2 19 72
VB.Net - CSV to Oracle table 4 29
automatic email alert 1 21
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now