SIMPLE Data Types

This entry is part 1 of 8 in the series New Pl/Sql features 11g

Oracle introduced new datatype in release 1 of 11g. The simple stuf!

  • SIMPLE_INTEGER; subtype of PLS_INTEGER
  • SIMPLE_FLOAT; subtype of BINARY_FLOAT
  • SIMPLE_DOUBLE; subtype of BINARY_DOUBLE

These simple datatypes have some extra restrictions, but if in your case these aren’t important they make up for it with a huge performance gain when compiled native. Steven Feuerstein reports 300% speeds gains when compiled native. When compiled interpreted the performance gain is far less, but still 6%.

SIMPLE type compared to their basetype

  • Same range as basetype
  • But added NOT NULL constraint
  • SIMPLE_INTEGER also has no overflow error

Advantages compared to basetype

  • Much faster when compiled native (PLSQL_CODE_TYPE=’NATIVE’)
  • Little faster when run interpreted (PLSQL_CODE_TYPE=’INTERPRETED’)

When should you use these datatypes?

  • You have some heavy arithmetic and your are sure you’re values will never be null
  • And in case of SIMPLE_INTEGER your values will never be out of range
  • (preferably when you run native code, which will bring you the most performance gain)
Series NavigationCONTINUE Statement >>

Posted in Pl/Sql Tagged with: , ,

Leave a Reply