Sysaux tablespace is too big

Rank or distinct, which one you like to use

first impression, distinct is lighter, rank is more expensive though it is new and cool. likes to see some document explain how each works. execution plan shows  somewhat though.

The story:

I got a query from someone to run on my oracle server(realize the rank is used in SQL server 2012 too), it basically likes to export the number of users who logged in during a month against his database table auditevent(every login has a record),  so count only once if a user logged in more than once.

he is using rank over and count the rank=1 so gives distinct value. his uses 2.697s, I  replaced it with distinct clause which uses 2.60s, the time runs 0.097 second less. I thought should be much more less.

select count(userid) from (select USERID ,
             RANK() OVER (PARTITION BY USERID ORDER BY LOGGEDTIME DESC,rownum) Rank_Line     
            from CONCERTOAUDIT.AUDITEVENT
            where ...)

where Rank_Line=1

replaced with

 select count( distinct USERID) 
        from
        (
            select USERID    
            from CONCERTOAUDIT.AUDITEVENT
            where..)