Oracle :- Numbers Table

Background

Let us create and populate a numbers table.

Code

Outline

Creating a numbers table is a bit more complicated.

And, it’s complexity is dependent on Version of Oracle.

Steps

  1. Create Table
    • Identity Column
      • To me identity column is a bit easier that Sequence
  2. Run Statement N Times
    • In Transact SQL, we use go
    • In Oracle
      • We can use the Hierarchy function “connect by level”
      • For Statement

SQL

SQL – Identity – Assign Default


create table "number"
    (
        "id" INTEGER
            GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1)
            NOT NULL

        , constraint "PK_Lab_Number"
            primary key
            (
                "id"
            )
    )

;
/

truncate table "number";
/

DECLARE
    l_time PLS_INTEGER;
    l_cpu PLS_INTEGER;

begin

    l_time := DBMS_UTILITY.get_time;
    l_cpu := DBMS_UTILITY.get_cpu_time;

    FOR iLoop IN 1..5000
    LOOP

        insert into "number"
        (
	        "id"
        )
        values
        (
	        default
        );

	END LOOP;

   DBMS_OUTPUT.put_line
      (
        'Time = ' || TO_CHAR(DBMS_UTILITY.get_time - l_time) || ' hsecs '
      );

   DBMS_OUTPUT.put_line
        (
          'CPU Time = ' || (DBMS_UTILITY.get_cpu_time - l_cpu) || ' hsecs '
        );

end;

/

drop table "number";
/

SQL – Identity – Assign Explicit


create table "number"
    (
        "id" INTEGER
            -- GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1)
            GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1)
            NOT NULL

        , constraint "PK_Lab_Number"
            primary key
            (
                "id"
            )
    )

;
/

truncate table "number";
/

DECLARE
        l_time     PLS_INTEGER;
        l_cpu      PLS_INTEGER;
        lMaxNumber integer;

begin

    l_time := DBMS_UTILITY.get_time;
    l_cpu := DBMS_UTILITY.get_cpu_time;

    FOR iLoop IN 1..1
    LOOP

		insert into "number"
        (
	        "id"
        )
        with cte
		(
		    "vail"
		)
		as
		(
		    select rownum
            FROM   dual
            CONNECT BY LEVEL <= 5000
		)
        select
           cte."vail"

        from cte
		;

	END LOOP;

    DBMS_OUTPUT.put_line
      (
        'Time = ' || TO_CHAR(DBMS_UTILITY.get_time - l_time) || ' hsecs '
      );

   DBMS_OUTPUT.put_line
        (
          'CPU Time = ' || (DBMS_UTILITY.get_cpu_time - l_cpu) || ' hsecs '
        );	

    select max("id")
    into   lMaxNumber
    from   "number"
    ;

   DBMS_OUTPUT.put_line
        (
          'lMaxNumber = ' || TO_CHAR(lMaxNumber)
        );	    

end;

/

drop table "number";
/

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s