INDEX_DESC - Oracle

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?
LVL 1
NeoTekAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

paquicubaCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
paquicubaCommented:
I meant Ïn your case
0
NeoTekAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.