Forum Stats

  • 3,872,115 Users
  • 2,266,381 Discussions
  • 7,911,051 Comments

Discussions

Use column references in SQL

ProDBA
ProDBA Member Posts: 120 Blue Ribbon

Dear Community,

I want to create a summarised database backup report, which includes data from multiple database tables which has no relation to each other. For example, I want to include the name of the database from v$database view:

SQL> Select name from v$database;

And the size of the database:

SQL> SELECT sum(bytes) FROM v$datafile;

And consider there is no common column between those two views.

Beside that I want to include the database size in Bytes, KBs, MBs ,......, and so on.

I am using the following query:

SQL> SELECT 
        "Database", "DB_SIZE_IN_Bytes", "DB_SIZE_IN_KBs", "DB_SIZE_IN_MBs", 
        "DB_SIZE_IN_GBs", "DB_SIZE_IN_TBs"
FROM (
    SELECT 
           (SELECT name FROM v$database ) "Database",
           (SELECT sum(bytes) FROM v$datafile ) "DB_SIZE_IN_Bytes",
           (SELECT sum(bytes)/1024 FROM v$datafile ) "DB_SIZE_IN_KBs",
           (SELECT sum(bytes)/1024/1024 FROM v$datafile ) "DB_SIZE_IN_MBs",
           (SELECT sum(bytes)/1024/1024/1024 FROM v$datafile ) "DB_SIZE_IN_GBs",
           (SELECT sum(bytes)/1024/1024/1024/1024 FROM v$datafile ) "DB_SIZE_IN_TBs"
    FROM dual
    );

In this way I am avoiding the join condition between v$database and v$datafile.

This is just an example, however, in my project I have more than 10 tables. Most of them without a common column.

The question is while converting bytes into kbs, mbs, and so on, I want to use the column references such as:

(SELECT DB_SIZE_IN_Bytes/1024 FROM dual ) "DB_SIZE_IN_KBs",
(SELECT DB_SIZE_IN_KBs /1024 FROM dual ) "DB_SIZE_IN_MBs",
(SELECT DB_SIZE_IN_MBs /1024 FROM dual ) "DB_SIZE_IN_GBs",
(SELECT DB_SIZE_IN_GBs /1024 FROM dual ) "DB_SIZE_IN_TBs"

instead of again and again calculations. Is there any way to use the column references such as mentioned above in the example instead of re-calculation?


Regards,

Ali

Best Answer

  • ProDBA
    ProDBA Member Posts: 120 Blue Ribbon

    Many thanks both of you! I still did not tried the cross-join approach, because as I mentioned in actual I have more than 10 tables. I went to another approach using sub-queries. The solution is as follows:

    SELECT (SELECT name FROM v$database ) as "Database",
            DB_SIZE_IN_Bytes,
            DB_SIZE_IN_Bytes / 1024 as DB_SIZE_IN_KBs,
    FROM (SELECT sum(bytes) as DB_SIZE_IN_Bytes FROM v$datafile ) x;
    

    Someone on stackoverflow mentioned this approach and I modified it according to my needs. This worked for me.

Answers

  • BEDE
    BEDE Oracle Developer Member Posts: 2,475 Gold Trophy

    Well, there is what is called common-table expression or query factoring.

    Something like:

    with dbs as  (  
        SELECT 
               (SELECT name FROM v$database ) "Database",
               (SELECT sum(bytes) FROM v$datafile ) "DB_SIZE_IN_Bytes",
               (SELECT sum(bytes)/1024 FROM v$datafile ) "DB_SIZE_IN_KBs",
               (SELECT sum(bytes)/1024/1024 FROM v$datafile ) "DB_SIZE_IN_MBs",
               (SELECT sum(bytes)/1024/1024/1024 FROM v$datafile ) "DB_SIZE_IN_GBs",
               (SELECT sum(bytes)/1024/1024/1024/1024 FROM v$datafile ) "DB_SIZE_IN_TBs"
        FROM dual
    )
    select  ...
    from dbs 
    cross join ...
    


    ProDBA
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,714 Red Diamond
    edited Jul 13, 2021 12:01PM

    Hi, @ProDBA

    Like Bede, I would use CROSS JOIN. In this case, I don't see any reason for scalar sub-queries or calling SUM (bytes) multiple times. I would do it like this:

    WITH  v$datafile_summary  AS
    (
      SELECT  SUM (bytes)   AS db_size_in_bytes
      FROM    v$datafile
    )
    SELECT   d.name				        AS database
    ,	 f.db_size_in_bytes
    ,	 f.db_size_in_bytes /        1024	AS db_size_in_kbs
    ,	 f.db_size_in_bytes / POWER (1024, 2)	AS db_size_ib_mbs
    ,	 f.db_size_in_bytes / POWER (1024, 3)	AS db_size_ib_gbs
    ,	 f.db_size_in_bytes / POWER (1024, 4)	AS db_size_ib_tbs
    FROM	   v$database         d
    CROSS JOIN v$datafile_summary f
    ;
    

    Do you really need the same total measure five diffferent ways (bytes, kbs, mbs, gbs and tbs)?

    ProDBA
  • ProDBA
    ProDBA Member Posts: 120 Blue Ribbon

    Many thanks both of you! I still did not tried the cross-join approach, because as I mentioned in actual I have more than 10 tables. I went to another approach using sub-queries. The solution is as follows:

    SELECT (SELECT name FROM v$database ) as "Database",
            DB_SIZE_IN_Bytes,
            DB_SIZE_IN_Bytes / 1024 as DB_SIZE_IN_KBs,
    FROM (SELECT sum(bytes) as DB_SIZE_IN_Bytes FROM v$datafile ) x;
    

    Someone on stackoverflow mentioned this approach and I modified it according to my needs. This worked for me.

  • BluShadow
    BluShadow Member, Moderator Posts: 42,540 Red Diamond

    I would also avoid using double quotes around the column aliases as that fixes the case of the names to exactly what you've typed and then you'll have to make sure you type it exactly the same again when you want to reference those columns. Leave off the double quotes and make your life easier. ;)

    ProDBAEdStevens
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,714 Red Diamond

    Hi,

     I still did not tried the cross-join approach, because as I mentioned in actual I have more than 10 tables.

    Why can't you use cross join with 10 tables?

  • ProDBA
    ProDBA Member Posts: 120 Blue Ribbon

    I think the query will be more complicated. But let me try with cross-join also and compare them both in-terms of performance and response time.

  • EdStevens
    EdStevens Member Posts: 28,778 Gold Crown

    Frank Kulash:

    Why can't you use cross join with 10 tables?


    ProDBA

    I think the query will be more complicated

    Well, "more complicated" is rather inherent when expanding a query from two tables to ten . . .