Querying time higher with 'Where' than without it

Question

I have something what I think is a srange issue. Normally, I think that a Query should last less time if I put a restriction (so that less rows are processed). But I don't know why, this is not the case. Maybe I'm putting something wrong, but I don't get error; the query just seems to run 'till infinity'.

This is the query

SELECT
    A.ENTITYID AS ORG_ID,
    A.ID_VALUE AS LEI,
    A.MODIFIED_BY,
    A.AUDITDATETIME AS LAST_DATE_MOD
FROM (
SELECT 
    CASE WHEN IFE.NEWVALUE IS NOT NULL 
        then EXTRACTVALUE(xmltype(IFE.NEWVALUE), '/DocumentElement/ORG_IDENTIFIERS/ID_TYPE')
        ELSE NULL
    end as ID_TYPE,
    case when IFE.NEWVALUE is not null 
        then EXTRACTVALUE(xmltype(IFE.NEWVALUE), '/DocumentElement/ORG_IDENTIFIERS/ID_VALUE')
        ELSE NULL
    END AS ID_VALUE,
    (select u.username from admin.users u where u.userid = ife.analystuserid) as Modified_by,
    ife.* 
FROM ife.audittrail ife
WHERE   
    --IFE.AUDITDATETIME >= '01-JUN-2016' AND
    attributeid = 499
    AND ROWNUM <= 10000
    AND (CASE WHEN IFE.NEWVALUE IS NOT NULL then EXTRACTVALUE(xmltype(IFE.NEWVALUE), '/DocumentElement/ORG_IDENTIFIERS/ID_TYPE') ELSE NULL end) = '38') A 
--WHERE A.AUDITDATETIME >= '01-JUN-2016';

So I tried with the two clauses commented (one per each time of course). And with both of them happens the same; the query runs for so long time that I have to abort it.

Do you know why this could be happening? How could I do, maybe in a different way, to put the restriction?

The values of the field AUDITDATETIME are '06-MAY-2017', for example. In that format.

Thank you very much in advance

Answer 1

I think you may misunderstand how databases work.

Firstly, read up on EXPLAIN - you can find out exactly what is taking time, and why, by learning to read the EXPLAIN statement.

Secondly - the performance characteristics of any given query are determined by a whole range of things, but usually the biggest effort goes not in processing rows, but finding them.

Without an index, the database has to look at every row in the database and compare it to your where clause. It's the equivalent of searching in the phone book for a phone number, rather than a name (the phone book is indexed on "last name").

You can improve this by creating indexes - for instance, on columns "AUDITDATETIME" and "attributeid".

Unlike the phone book, a database server can support multiple indexes - and if those indexes match your where clause, your query will be (much) faster.

Finally, using an XML string extraction for a comparison in the where clause is likely to be extremely slow unless you've got an index on that XML data.

This is the equivalent of searching the phone book and translating the street address from one language to another - not only do you have to inspect every address, you have to execute an expensive translation step for each item.