Okay, that took some work. You owe me 1/2 hour of billable time :)

Some caveats first.

- I don't have the model, so this may need some tweaking.
- There is a better solution IF you have Oracle 8.1.6.2 or better and IF you can use dynamic SQL. If both of these are true, then I'll re-write it to be even faster :)
- The crux of your issue had to do with the <> 2. Oracle basically cannot and will not use an index for an inequality. Changing that to IN (1,3) will allow the use of your bitmap index ( a regular B*Tree would not be used in either case - not selective enough)
- The other changes I made should help dramatically as well.

SELECT
---TFull.LIST_ID, TFull.TITLE, TFull.SHORT_DESC, TFull.RATE, TFull.LIST_TYPE_ID,
---TFull.AVG_GRADE, TFull.RELIABILITY_PCT, TFull.ACTIVITY,
---UA.USERNAME, DECODE ( TFull.LIST_TYPE_ID, 2, LR.LENGTH, NULL) AS ACTIVITY
FROM
---(
------SELECT
---------LIST_ID, TITLE, SHORT_DESC, RATE, LIST_TYPE_ID,
---------AVG_GRADE, RELIABILITY_PCT, ACTIVITY
------FROM
---------(
---------SELECT
------------ROWNUM RN, LIST_ID, TITLE, SHORT_DESC, RATE, LIST_TYPE_ID,
------------AVG_GRADE, RELIABILITY_PCT, ACTIVITY
---------FROM
------------(
------------SELECT
---------------L.LIST_ID, L.TITLE, L.SHORT_DESC, L.RATE, T.LIST_TYPE_ID,
---------------L.AVG_GRADE, L.RELIABILITY_PCT,
---------------(L.CALLS_RECEIVED + L.NUM_PAID_EMAILS - L.CALLS_FAILED) ACTIVITY,
------------FROM
---------------TLISTING------L
------------WHERE
---------------------L.LIST_TYPE_ID ---IN---(1, 3)
---------------AND---L.LIST_STATUS_ID ---=---0
---------------AND---L.CATEGORY_ID ------IN
---------------------------------------(
------------------------------------------SELECT
---------------------------------------------CATEGORY_ID
------------------------------------------FROM
---------------------------------------------TCATEGORY
------------------------------------------START WITH
---------------------------------------------CATEGORY_ID = 10
------------------------------------------CONNECT BY PRIOR
---------------------------------------------CATEGORY_ID = PARENT_ID
---------------------------------------)
---------------ORDER BY
------------------L.AVG_GRADE ---------------------------------------------DESC,
------------------L.RELIABILITY_PCT ---------------------------------------DESC,
------------------(L.CALLS_RECEIVED + L.NUM_PAID_EMAILS - L.CALLS_FAILED) DESC
------------)---T1
---------WHERE
------------ROWNUM <= 15 -- This would be variable obviously
---------)---T2
------WHERE
---------RN >= 1----- This would be variable obviously
------)---T3

---UNION ALL -- Can use all since they definitely have different list type id - saves time

------SELECT
---------LIST_ID, TITLE, SHORT_DESC, RATE, LIST_TYPE_ID,
---------AVG_GRADE, RELIABILITY_PCT, ACTIVITY
------FROM
---------(
---------SELECT
------------ROWNUM RN, LIST_ID, TITLE, SHORT_DESC, RATE, LIST_TYPE_ID,
------------AVG_GRADE, RELIABILITY_PCT, ACTIVITY
---------FROM
------------(
------------SELECT
---------------L.LIST_ID, L.TITLE, L.SHORT_DESC, L.RATE, T.LIST_TYPE_ID,
---------------L.AVG_GRADE, L.RELIABILITY_PCT,
---------------(L.CALLS_RECEIVED + L.NUM_PAID_EMAILS - L.CALLS_FAILED) ACTIVITY,
------------FROM
---------------TLISTING------L
------------WHERE
---------------------L.LIST_TYPE_ID ---=---2
---------------AND---L.LIST_STATUS_ID ---=---0
---------------AND---L.CATEGORY_ID ------IN
---------------------------------------(
------------------------------------------SELECT
---------------------------------------------CATEGORY_ID
------------------------------------------FROM
---------------------------------------------TCATEGORY
------------------------------------------START WITH
---------------------------------------------CATEGORY_ID = 10
------------------------------------------CONNECT BY PRIOR
---------------------------------------------CATEGORY_ID = PARENT_ID
---------------------------------------)
---------------ORDER BY
------------------L.AVG_GRADE ---------------------------------------------DESC,
------------------L.RELIABILITY_PCT ---------------------------------------DESC,
------------------(L.CALLS_RECEIVED + L.NUM_PAID_EMAILS - L.CALLS_FAILED) DESC
------------)---T4
---------WHERE
------------ROWNUM <= 10 -- This would be variable obviously
---------)---T5
------WHERE
---------RN >= 1----- This would be variable obviously
------)---T6
---)---------------TFull---,
---TUSERAUTH ------UA------,----- Moved this join until after the recordset is cut to 25 record
---TLISTRECORDING LR -------- ---to save time. Also removed TCATEGORY, since it wasn't being
-----------------------------------used
WHERE
---------UA.USER_ID------=---L.USER_ID---
---AND---D.LIST_ID---(+)=---L.LIST_ID---
ORDER BY
---LIST_TYPE_ID---------------------------------------------,
---AVG_GRADE ------------------------------------------DESC---,
---RELIABILITY_PCT ------------------------------------DESC---,
---DECODE ( TFull.LIST_TYPE_ID, 2, LR.LENGTH, NULL)---DESC


Change '---' to 3-char tab and all will line up nicely

Hope this helps (actually, hope this works :) )

- Chris