Like this:
create table THIS_TABLE (
id number NOT NULL,
constraint THIS_TABLE_PK PRIMARY KEY(id)
USING INDEX TABLESPACE INDEX_TABLESPACE
) tablespace DATA_TABLESPACE;
USING INDEX TABLESPACE is the syntax - you weren't far off.
As far as good/bad practice is concerned, that's opinion-based, so not really something that should be asked here. The alternative is to use the ALTER TABLE .... syntax after table creation.
Your combinations in order of typical performance:
1. > 2. > 4. ( > 3.)
3. is invalid. If rows are only unique per (code, class_id), the lookup by code alone can return multiple rows and is different from the rest.
2. is pointless. If code is unique, there is no point in adding another predicate on class_id - except to verify that a given code actually belongs to a given class_id (and get no row otherwise).
Only 1. and 4. make sense and I would go with 1., of course. Unless you have additional requirements for the values of code, it's much more efficient to have one unique column. You could also make it the PK. Queries are simpler (one predicate instead of two), the (automatically created) unique index is potentially smaller (the most important factor here!), the lookup is typically slightly faster.
UPDATEs are also potentially more expensive for 2., where more columns trigger index updates. An UPDATE changing only code_id is cheaper for 1..
Your test result for 1. is counter-intuitive, maybe an artifact of your specific setup. Maybe you didn't prewarm the cache? Or some other random factor. It's pretty obvious from the EXPLAIN output: the only difference between 1. and 2. is the additional Filter: (class_id = 1) step. Nothing to gain here, you can only lose (even if very little in this case). 2. is typically a bit slower than 1. And 4. is also typically a bit slower than 1.
Best Answer
Try this:
254 total characters is from Errata ID: 1690 with 64 before the @ and up to 255 after it.
This simple regexp, email address must have a @, will alert you to attempts to insert wildly wrong data (perhaps helping catch a bug or two). However, building a complete RFC 5322 validator is a difficult task. For details take a look here:
https://stackoverflow.com/questions/201323/using-a-regular-expression-to-validate-an-email-address