2
There is no way to do that.
The best I can think of is to set the parameter statement_timeout in your database session so that statements that run too long fail with an error.
2
Use this one:
SELECT TABLE_NAME, PARTITIONING_TYPE, COLUMN_NAME, DATA_TYPE
FROM USER_PART_TABLES
JOIN USER_PART_KEY_COLUMNS ON NAME = TABLE_NAME
JOIN USER_TAB_COLS USING (TABLE_NAME, COLUMN_NAME)
where OBJECT_TYPE = 'TABLE';
2
your create index statement needs the ON clause to use the partition scheme you've created:
create clustered index [CCIX_Comments] ON [dbo].[Comments]
( [year] ASC )
ON [scheme_Comments]([year])
2
The 5x performance hit for query 3 turned out as mistake. That was the puzzling bit and is resolved.
All the rest makes sense, though it's currently not going your way. The PRIMARY KEY is the culprit:
PRIMARY KEY (scedtime, node_id)
The manual suggests:
Create an index on the key column(s), as well as any other indexes you might want, on the partitioned ...
Only top voted, non community-wiki answers of a minimum length are eligible